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.
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.YYYYMMWHERE 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, sportsguyMS Access 20 years, SQL hack |
|
sportsguy
Starting Member
39 Posts |
Posted - 2013-12-03 : 17:21:40
|
Thanks for reading! I solved the problemSELECT 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.YYYYMMWHERE bil.FiscalPeriod > 201200 and fx.Year = 2014 AND bil.NationalLocal = 'N' and bil.Billed <> 0 GROUP BY fc.FY, bil.Invoice_Nbr) nqGROUP BY nq.FY; Hack yes, slow yes, eventually get the answer? yes. .MS Access 20 years, SQL hack |
|
|
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 nqgroup by nq.FY Be One with the OptimizerTG |
|
|
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 |
|
|
|
|
|
|
|