DHEERAJ SONI DHEERAJ SONI - 1 year ago 45
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 Source

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;