Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A little help with the error msg

Author  Topic 

sportsguy
Starting Member

39 Posts

Posted - 2013-12-03 : 17:10:14
Thnsk in advance!


SELECT nq.FY, count(nq.Invoice_Nbr), sum(nq.Billed_USD)
FROM
(
SELECT fc.FY, bil.Invoice_Nbr, sum(bil.Billed * fx.FXRate) as "Billed_USD"
FROM [dbo].[BILLABLE] bil
INNER JOIN dbo.DISTRICTS d ON bil.strDistrict = d.strDistrict
INNER JOIN dbo.FX fx ON d.Currency = fx.Currency
INNER JOIN dbo.FISCAL_CALENDAR fc ON bil.FiscalPeriod = fc.YYYYMM
WHERE bil.FiscalPeriod > 201200 and fx.Year = 2014 AND bil.NationalLocal = 'N' and bil.Billed <> 0
GROUP BY fc.FY, bil.Invoice_Nbr
) AS nq


Error message:
Column 'nq.FY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ummm, I don't quite understand the error message. . .

Thanks, sportsguy

MS Access 20 years, SQL hack

sportsguy
Starting Member

39 Posts

Posted - 2013-12-03 : 17:21:40
Thanks for reading! I solved the problem


SELECT nq.FY, count(nq.Invoice_Nbr), sum(nq.Billed_USD)
FROM
(
SELECT fc.FY, bil.Invoice_Nbr, sum(bil.Billed * fx.FXRate) as "Billed_USD"
FROM [dbo].[BILLABLE] bil
INNER JOIN dbo.DISTRICTS d ON bil.strDistrict = d.strDistrict
INNER JOIN dbo.FX fx ON d.Currency = fx.Currency
INNER JOIN dbo.FISCAL_CALENDAR fc ON bil.FiscalPeriod = fc.YYYYMM
WHERE bil.FiscalPeriod > 201200 and fx.Year = 2014 AND bil.NationalLocal = 'N' and bil.Billed <> 0
GROUP BY fc.FY, bil.Invoice_Nbr
) nq
GROUP BY nq.FY;


Hack yes, slow yes, eventually get the answer? yes. .

MS Access 20 years, SQL hack
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-03 : 17:23:22
well you were faster than me to post the solution

you are just missing the final GROUP BY:

SELECT nq.FY, count(nq.Invoice_Nbr), sum(nq.Billed_USD)
FROM
(
SELECT fc.FY, bil.Invoice_Nbr, sum(bil.Billed * fx.FXRate) as "Billed_USD"
FROM [dbo].[BILLABLE] bil
INNER JOIN dbo.DISTRICTS d ON bil.strDistrict = d.strDistrict
INNER JOIN dbo.FX fx ON d.Currency = fx.Currency
INNER JOIN dbo.FISCAL_CALENDAR fc ON bil.FiscalPeriod = fc.YYYYMM
WHERE bil.FiscalPeriod > 201200 and fx.Year = 2014 AND bil.NationalLocal = 'N' and bil.Billed <> 0
GROUP BY fc.FY, bil.Invoice_Nbr
) AS nq
group by nq.FY


Be One with the Optimizer
TG
Go to Top of Page

Paul J. Brooks

7 Posts

Posted - 2013-12-05 : 07:31:43
This is a good approach for every person.i think this is very important part perform in the programming.Thanks for sharing this information.Programing is very important thing in the currently situation.
unspammed
Go to Top of Page
   

- Advertisement -