SQL: Subtract two columns

sql

Solution

Just add:

SUM(case when price >= 0 THEN 1 ELSE 0 END) - 
    SUM(case when price < 0 THEN 1 ELSE 0 END) AS NetCount

as your last statement, so you'd end up with this:

select content_type_code_id 
    , ABS(price) AS price
    , SUM(case when price >= 0 THEN 1 ELSE 0 END) AS debits
    , SUM(case when price < 0 THEN 1 ELSE 0 END) AS credits
    , SUM(case when price >= 0 THEN 1 ELSE 0 END) - 
        SUM(case when price < 0 THEN 1 ELSE 0 END) AS NetCount
from dbo.transaction_unrated  
where transaction_date >= '2012/05/01'
      and transaction_date < '2012/06/01'
      and content_provider_code_id in (1)
group by content_type_code_id, ABS(price) 
ORDER BY ABS(price) ASC

Derived table version for Lamak:

You can also use a derived table to make the code a little cleaner:

select content_type_code_id,
    price, debits, credits, (debits - credits) as NetCount
from (
    select content_type_code_id 
        , ABS(price) AS price
        , SUM(case when price >= 0 THEN 1 ELSE 0 END) AS debits
        , SUM(case when price < 0 THEN 1 ELSE 0 END) AS credits
    from dbo.transaction_unrated  
    where transaction_date >= '2012/05/01'
          and transaction_date < '2012/06/01'
          and content_provider_code_id in (1)
    group by content_type_code_id, ABS(price) 
) YourDerivedTable
ORDER BY price ASC

Problem

``` select content_type_code_id , ABS(price) AS price , SUM(case when price >= 0 THEN 1 ELSE 0 END) AS debits , SUM(case when price < 0 THEN 1 ELSE 0 END) AS credits from dbo.transaction_unrated where transaction_date >= '2012/05/01' and transaction_date < '2012/06/01' and content_provider_code_id in (1) group by content_type_code_id, ABS(price) ORDER BY ABS(price) ASC ``` The above query produces the following output: ``` content_type_code_id price debits credits 1 0.00 317 0 1 0.99 178 1 1 1.99 786 1 ``` But I want something like this: ``` content_type_code_id price debits credits NetCount 1 0.00 317 0 317 1 0.99 178 1 177 1 1.99 786 1 785 ``` Where NetCount = (debits - credits) When I try to create another column for that I get an error.

Original source