Group Error. - SQL Server Forums

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

Group Error.

Author

Topic

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-03-24 : 16:53:56

I have the following. It is not working. I get the [Err] 1111 - Invalid use of group function

select Fios.Date as Date, Fios.iao_id as IO, sum(Fios.sales) as Sales, sum(fios.cash) as cash,
z.zone, geo.city, geo.zip

from Fios_salesdata fios, fios_geoography_data geo, Fios_Zones z

where Fios.zone_id = z.id
and Fios.camp_id = geo.camp_id
having sum(fios.sales) >1000
group by Fios.Date, Fios.iao_id, sum(Fios.sales),sum(Fios.cash), z.zone, geo.city,geo.zip

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 17:03:37

having needs to be AFTER the Group By clause...

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

fengfeng
Yak Posting Veteran

64 Posts

Posted - 2011-03-24 : 17:26:13

quote:
Originally posted by Seventhnight

having needs to be AFTER the Group By clause...

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

I removed the having but I still get the error.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-24 : 17:32:44

Try this:

SELECT Fios.Date AS DATE, Fios.iao_id AS IO, 
SUM(Fios.sales) AS Sales, SUM(fios.cash) AS cash,
z.zone, geo.city, geo.zip
FROM Fios_salesdata fios
INNER JOIN Fios_Zones z ON Fios.zone_id = z.id
INNER JOIN fios_geoography_data geo ON Fios.camp_id = geo.camp_id 
GROUP BY Fios.Date, Fios.iao_id, z.zone, geo.city, geo.zip
HAVING SUM(fios.sales) >1000

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 17:33:43

Sorry.. didn't read the whole query.

select Fios.Date as Date, Fios.iao_id as IO, sum(Fios.sales) as Sales, sum(fios.cash) as cash,
z.zone, geo.city, geo.zip

from Fios_salesdata fios, fios_geoography_data geo, Fios_Zones z

where Fios.zone_id = z.id
and Fios.camp_id = geo.camp_id
group by Fios.Date, Fios.iao_id, ~~sum(Fios.sales),sum(Fios.cash),~~ z.zone, geo.city,geo.zip
having sum(fios.sales) >1000

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources