1

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!

McNets
  • 10,352
  • 3
  • 32
  • 61
Marce Castro
  • 35
  • 2
  • 9

2 Answers2

1

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.

Utsav
  • 7,914
  • 2
  • 17
  • 38
  • Great Thanks! I'm going to study a little on it :-) BTW, how do I pick it as best answer? Can find anything (maybe because of my little reputation) – Marce Castro May 28 '17 at 09:54
  • Please read [What should I do when someone answers my question](https://stackoverflow.com/help/someone-answers). You have to click on the `tick` on the left of the answer. There is no restriction on reputation for accepting the answer, however for upvote, you need at least 10 reputation. – Utsav May 28 '17 at 09:56
  • 1
    Sorry! didn't notice that. Thanks for the link. – Marce Castro May 28 '17 at 09:57
  • I don't think it's a good answer. It only contains a (subjective) advice to not use number aliases, which were not the source of the original problem (which was referring to unaliased `clicks` from `order by`). At most it might have made a [good comment](https://stackoverflow.com/questions/7392730/what-does-sql-clause-group-by-1-mean#comment43572682_7392730). – GSerg May 28 '17 at 10:03
  • As I mentioned in answer as well I am not sure why first error came as the query looks correct. However I gave reason for second error and also approach to get his expected output. – Utsav May 28 '17 at 10:09
0

try this 

SELECT company_name, SUM(clicks)

FROM table1

WHERE code = 'ES'

GROUP BY company_name

ORDER BY 2 DESC;
ragav
  • 66
  • 7