CODEWITHSUNDEEP
sql
mariner
mariner

Reputation: 930

SQL: Subtract two columns

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.

Upvotes: 5

Views: 74550

Answers (3)

Grace
Grace

Reputation: 177

Good evening. I had a similar task and found it to be very short just to add a column and update that.

I think this can be done after your code generates the database. If it doesn't work for your situation, I would appreciate feedback on that.

ALTER TABLE name of table here ADD NetCount integer;

UPDATE name of table here SET NetCount=Debits-Credits;

Notes:

  • first line adds a column called NetCount of type integer
  • second line updates it to be the difference between debits and credits
  • sql commands are in caps, information specific to you is in italics

Good luck!

Upvotes: 1

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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

Upvotes: 8

Baaju
Baaju

Reputation: 2012

WITH tbl AS 
(
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  
)

SELECT content_type_code_id, proce, debits, credits, (debits - credits) netcount from tbl

Upvotes: 1

Related Questions