Aggregate on the table but need to skip 0 values

Question

I have a table with transaction that have a field for qty and another field to indicate if its a buy or sell, for the respective item.

I am trying to write an aggregate query that gives me a sum of each of the items.

My table looks like this:

ID    Item     Qty    Buy_sell    price
1     item1    5      1           2.5
2     item1    4      0           3.2
3     item2    8      1           155.25
4     item3    179    1           89.75
5     item1    18     1           3.1
4     item3    179    0           93.25

And my query looks like this:

Select 
    Item,
    sum(case when Buy_sell=1 then Qty when Buy_sell=0 then Qty*-1 else 0 end) as Balance 
from Table1 
group by Item 
order by Item

So far so good.

Output:

Item     Balance
Item1    19 
Item2    8
Item3    0

I want to avoid rows where Total is 0. I'm doing this on SQL Server CE.

Answer 1

You have to place a condition in the HAVING clause:

Select Item, sum(case 
                   when Buy_sell=1 then Qty 
                   when Buy_sell=0 then Qty*-1 
                   else 0 
             end) as Balance 
from Table1 
group by Item 
having sum(case 
              when Buy_sell=1 then Qty 
              when Buy_sell=0 then Qty*-1 
              else 0 
           end) <> 0
order by Item