How to use sum in where clause in SQL?

user_8176451 0
Viewed 2257

I want to execute this query:

Select Environment, SUM(Scores) as `Scores`,SUM(Clicks) as `Clicks` 
      from Example.table 
      where Scores > sum(Scores)
      group by Environment

But it throws the following error:

Aggregate function SUM not allowed in WHERE clause at [4:27]

whereas for the case of having: having Scores > sum(Scores) doesn't allow me to keep Scores. What is the right way to implement this?

sql
2 Answers

Your query should be like this:

Select Environment
       ,Scores
       ,SUM(Scores) as `Scores`
       ,SUM(Clicks) as `Clicks`  
       from TABLE 
      group by Environment, Scores
      HAVING (Scores > sum(Scores))

You need to include Score and group by that also (even if you don't need it).

user_53636860

You can only use the HAVING clause for comparison:

GROUP BY ...
  HAVING Scores > sum(Scores)

So your SQL will be something like:

Select Environment, SUM(Scores) as `Scores`,SUM(Clicks) as `Clicks` 
      from Example.table 
      group by Environment
      HAVING Scores > sum(Scores)
user_86129350
Related