1
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

1
  • 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. May 31, 2014 at 18:13

1 Answer 1

3

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;
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.