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 ?

Answer 1

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