#1111 - Invalid use of group function

[bubbadawg]

I am using the following query in an attempt to get  total number(sum) of slides retrieving the max number from each project, however I am receiving the following error (#1111 - Invalid use of group function).

 

Projects              |    Slides

               

Air Stairs            |      15

Air Stairs            |      09

Baseball Game  |      22

Cargo                |      47

Cargo                |      31

 

Here's the query:

 

SELECT COALESCE(project,'Total') as Project, SUM(MAX(slides)) as Slides FROM projects_tbl WHERE date BETWEEN '2010-01-01' AND '2010-12-31' GROUP BY Project with ROLLUP

 

If I remove the SUM(), then the it works, however, I do not get an accurate total for all of the projects/slides.

 

Thanks in advance for any and all replies.

 

[fenway]

That's because the MAX() already uses the aggregated results -- I'm not sure what you're trying to do here.

[gizmola]

When you use an aggregate function it takes all the rows in the group and applies the aggregate to them.  In the case of SUM() and your example data:

 

Air Stairs            |      15

Air Stairs            |      09

 

One would expect that the sum(slides) will be 24.  You can't nest aggregate functions as that doesn't make sense.  MAX() simply returns the largest value in the max'd column. 

 

Adding the Rollup is simply going to give you one more row where all the rows will be added together.

 

As fenways stated... what are you trying to do?