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

Answer 1

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;