Invalid use of group function (with no conditions)

Question

The table looks like this:

| colA | colB | colC | colD |some other cols
-----------------------------
|double|double| int  | int  |some other datatyps

I want to select the sum of each column and group by them. At the moment I have no conditions wich musst be true. So this is my query:

SELECT SUM(colA) as 'cola', SUM(colB)as 'colb', SUM(colC) as 'colc', SUM(colD) as 'cold', SUM(colA - colB)as 'sub'
FROM table_name
GROUP BY cola,colb,colc,cold,sub

And then it creates the error : Invalid use of group function

I red several posts about this error, but they all had an WHERE condition and a HAVING solved this problem, but I dont have a condition. Any Ideas what could cause this error message ?

sagi · Accepted Answer · 2016-06-06T09:01:24.130

0

You don't need a GROUP BY clause at all :

SELECT SUM(t.colA) as 'cola',
       SUM(t.colB)as 'colb',
       SUM(t.colC) as 'colc',
       SUM(t.colD) as 'cold',
       SUM(t.colA - t.colB)as 'sub'
FROM table_name t

A GROUP BY clause is used to get calculated / aggregated / distinct value per GROUP , like an ID , since you want the total sum, there's no need for a group by clause

GROUP BY cola,colb,colc,cold,sub

This row means that each group is the combination of cola,colb,colc,cold,sub , which doesn't exists yet since sub is a calculated column! Don't include it in your group by

If you want to distinct duplicates, then either use DISTINCT , or drop the sub column from the group by :

GROUP BY t.cola,t.colb,t.colc,t.cold

edited Jun 06 '16 at 09:01

answered Jun 06 '16 at 08:50

sagi

40,026
6
59
84

Maybe at the moment but later I have an condition and then I need need to group – nova Jun 06 '16 at 08:51
Group by what? @Isabell – sagi Jun 06 '16 at 08:52
@Isabell then post that other query – Yuri Jun 06 '16 at 08:52
For what? To get distinct values? What's the purpose ? – sagi Jun 06 '16 at 08:54
Can't group on 'cola' – nova Jun 06 '16 at 08:58

score 0 · Answer 2 · answered Jun 06 '16 at 08:53

0

Your fault is that in the group by you include the sub that doesn't exist in the table.

So try this:

SELECT SUM(colA) as 'cola', SUM(colB)as 'colb', 
SUM(colC) as 'colc', SUM(colD) as 'cold', SUM(colA - colB)as 'sub'
FROM table_name
GROUP BY cola,colb,colc,cold

answered Jun 06 '16 at 08:53

Mark Roll

506
3
6
15

Can't group on 'cola' – nova Jun 06 '16 at 08:58
What DB you use? In simple SQL i used that simple code and works perfectly for me: create table ca (colA float,colB float,colC int,colD int) SELECT SUM(colA) as 'cola', SUM(colB)as 'colb', SUM(colC) as 'colc', SUM(colD) as 'cold', SUM(colA - colB)as 'sub' FROM ca GROUP BY cola,colb,colc,cold – Mark Roll Jun 06 '16 at 09:02
Aurora and use it via sequel pro – nova Jun 06 '16 at 09:11

Invalid use of group function (with no conditions)

2 Answers2