CODEWITHSUNDEEP
sqlsql-servert-sql
James
James

Reputation: 940

SQL Query to Calculate two Amounts on the same row

probably a very simple answer but i'm new to T-SQL so could do with some help!

I need a 3rd column that works out TotInc - (minus) TotEx to give me a TotalDisposableIncome

Here is my SQL:

--This gives me the Total Income and Total Expenditure on the same row

SELECT
    SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
    SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials

Thanks!

Upvotes: 2

Views: 324

Answers (4)

Narveson
Narveson

Reputation: 1111

SELECT
  SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc, 
  SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx,
  SUM(CASE 
    WHEN Type = '1' THEN Amount 
    WHEN Type = '2' Then -Amount 
    ELSE 0
  END) AS TotalDisposableIncome
FROM ClaimFinancials

Upvotes: 0

Narveson
Narveson

Reputation: 1111

There's more than one way to do it.

WITH T1 AS 
( 
  SELECT 
    Type,    
    SUM(Amount as Total 
  FROM ClaimFinancials 
) 
SELECT 
  Inc.Total as TotalInc, 
  Ex.Total as TotEx, 
  Inc.Total - Ex.Total AS TotalDisposableIncome 
FROM T1 Inc, T2 Ex
where T1.Type = 1 and T2.Type = 2

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838796

You could use a Common Table Expression (CTE):

WITH T1 AS
(
    SELECT
        SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
        SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
)
SELECT TotalInc, TotEx, TotalInc - TotEx AS TotalDisposableIncome
FROM T1

Or an ordinary subquery:

SELECT TotalInc, TotEx, TotalInc - TotEx AS TotalDisposableIncome
FROM
(
    SELECT
        SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
        SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
) T1

Upvotes: 4

bobs
bobs

Reputation: 22204

You can't reference your column aliases elsewhere in your SELECT clause. Here is one alternative.

SELECT TotalInc, TotEx, TotInc - TotEx as TotalDisposable
FROM (
    SELECT
    SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
    SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
    ) AS Total

Upvotes: 4

Related Questions