DHEERAJ SONI DHEERAJ SONI - 7 months ago 9
SQL Question

How to find net rows from sales and cancel rows using sql

I have a table with columns mentioned below:

transaction_type transaction_number amount

Sale 2016040433 50
Cancel R2016040433 -50
Sale 2016040434 50
Sale 2016040435 50
Cancel R2016040435 -50
Sale 2016040436 50


I want to find net number of rows with only sales which does not include canceled rows.
(Using SQL Only).

Answer

If you just want to count the sales and subtract the cancels (as suggested by your sample data), you can use conditional aggregation:

select sum(case when transaction_type = 'Sale' then 1
                when transaction_type = 'Cancel' then -1
                else 0
            end)
from t;