# 1111 - Invalid use of group function

Question

I use the following query, trying to get the total number (amount) of slides receiving the maximum amount from each project, however I get the following error (# 1111 - Improper use of group function). Here's the request:

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 delete SUM (), then it works, however, I do not get the exact result for all projects / slides.

Thanks in advance for any answers.

Answer 1

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

Answer 2

You can try something like:

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

You need to get max for each project, and after that you can summarize everything.