group by+where on calculated column

Question

SELECT
   SUM(a)-SUM(b) AS c1,
   SUM(a) AS c2,
   SUM(b) AS c3
FROM tbldoc
GROUP BY Cid 
WHERE c1<>0

If you take ( WHERE c1<>0 ) not error

Thank you Answer

While this question has certainly been asked before, I don't agree with which question this has been marked a duplicate of. In particular, there is a lack of understanding as to when to apply the where clause and when to apply the having clause which is fundamental here. — Colin 't Hart, May 31 '14 at 18:13

score 3 · Accepted Answer · answered May 31 '14 at 10:38

I presume you mean that you get an error with this query, and that if you remove the where clause you get no error, but not the right answer.

You need a having clause, which is like where but can be applied to the aggregated results:

select
  sum(a)-sum(b) AS c1,
  sum(a) AS c2,
  sum(b) AS c3
from tbldoc
group by Cid
having sum(a)-sum(b) <> 0;

Alternatively, you can move the whole statement to a subquery and perform the where on the outside:

select *
from (
  select
    sum(a)-sum(b) AS c1,
    sum(a) AS c2,
    sum(b) AS c3
  from tbldoc
  group by Cid
) s
where c1 <> 0;

1 Answers1