#1111 - Invalid use of group function

Question

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). 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.

Could you please post some sample data and desired output? – Quassnoi Jan 18 '11 at 17:33 — Quassnoi, Jan 18 '11 at 17:33

score 4 · Accepted Answer · answered Jan 18 '11 at 17:32

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

score 2 · Answer 2 · answered Jan 18 '11 at 17:30

You can try with 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 obtain the max for each project, and after this you can sum all.

#1111 - Invalid use of group function

2 Answers2

Linked

Related