CODEWITHSUNDEEP
sqlsql-server
Usman Farooq
Usman Farooq

Reputation: 123

how can i set the alias of column`s sum value based on another column in same table in sql?

I have a table of Accounts, having columns:

Acc_Id, Transaction_TypeId, Amount

I want to get result as When Transaction_TypeId = 1 then Sum of Amount as 'Total Advance Payments'.

Else when Transaction_typeId = 2 then Sum of Amount as 'Total Reciepts'

Here is my SQL query:

SELECT SUM(Amount) AS 'Sum' , Transaction_TypeId INTO #temp1  FROM AccountDetailTable WHERE  Account_MasterId = 1 GROUP BY Transaction_TypeId

SELECT Sum as 'Total Advance' from #temp1  WHERE #temp1.Transaction_TypeId = 1;
SELECT Sum as 'Total Cash Receipts' FROM #temp1  WHERE #temp1.Transaction_TypeId = 2;

DROP TABLE #temp1;

but this query returns me two different result sets. How can i get the values in same result sets?

Upvotes: 0

Views: 70

Answers (2)

sagi
sagi

Reputation: 40481

You should use CASE EXPRESSION like this:

SELECT
       sum(case when #temp1.Transaction_TypeId = 1 then amount else 0 end) as 'Total Advance',
       sum(case when #temp1.Transaction_TypeId = 2 then amount else 0 end) as 'Total Cash Receipts'
FROM #temp1

Upvotes: 1

Lamak
Lamak

Reputation: 70638

Use a CASE expression:

SELECT  SUM(CASE WHEN Transaction_TypeId = 1 THEN somecolumn END) as [Total Advance],
        SUM(CASE WHEN Transaction_TypeId = 2 THEN somecolumn END) as [Total Cash Receipts]
FROM #temp1;

Upvotes: 1

Related Questions