SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 A little help with the error msg
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
39 Posts

Posted - 12/03/2013 :  17:10:14  Show Profile  Reply with Quote
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

USA
39 Posts

Posted - 12/03/2013 :  17:21:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 12/03/2013 :  17:23:22  Show Profile  Reply with Quote
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
Starting Member

USA
7 Posts

Posted - 12/05/2013 :  07:31:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000