Can't fix this: "Cannot group by an aggregate"

1
votes

Sorry for the silly question. I have read a lot of threads about the same issue, but still, can't fix this...

SELECT   company_name, SUM(clicks)
FROM     table1
WHERE    code = 'ES'
GROUP BY 1
ORDER BY clicks DESC
LIMIT 100;

This results in:

Expression 'clicks' is not present in the GROUP BY list

And if I try this:

SELECT   company_name, SUM(clicks)
FROM     table1
WHERE    code = 'ES'
GROUP BY 1,2
ORDER BY clicks DESC
LIMIT 100;

This is what I get:

Cannot group by an aggregate.

If I try with no aggregation on "clicks":

SELECT   company_name, clicks
FROM     table1
WHERE    code = 'ES'
GROUP BY 1
ORDER BY clicks DESC
LIMIT 100;

Error: Expression 'clicks' is not present in the GROUP BY list

And if add clicks to the group by:

SELECT   company_name, clicks
FROM     table1
WHERE    code = 'ES'
GROUP BY 1,2
ORDER BY clicks DESC
LIMIT 100;

The results are not what I need:

Company_name | clicks
-------------+--------
    company1 | 250   
    company1 | 340
    company2 | 100 
    company2 | 300
    company2 | 344

How can I get?:

Company_name | clicks
-------------+-------
    company1 | 590
    company2 | 744

Thank you!

2
sqlgroup-by
It cannot sort by clicks, not group. – GSerg
Please, tag your RDBMS. – McNets
Sorry @McNets, it's a private company RDBMS. Can't really tell. – Marce Castro
And thanks for the formatting. I'll stick to it next time! – Marce Castro
@MarceCastro That does not make sense. If there is some DBMS that is written and used by this company and not known to anyone else, it would be offtopic to post any questions about it because no one would be able to answer. If it's a secret that some company uses MySql, then don't name this company, but do name MySql. – GSerg

2 Answers

1
votes

You should do this

SELECT company_name, SUM(clicks) as clicks
FROM table1   
WHERE code = 'ES'  
GROUP BY company_name 
ORDER BY clicks DESC 
LIMIT 100;

Your first query is correct, not sure why are you getting the error. Your second query is however incorrect as you cannot group by second column, which you have derived by aggregation.

Using numbers in group by clause, while looks neater, it actually adds to the confusion. Hence try to omit them completely. Use proper column alises and use them in group by and order by to avoid confusion.

0
votes

try this 

SELECT company_name, SUM(clicks)

FROM table1

WHERE code = 'ES'

GROUP BY company_name

ORDER BY 2 DESC;