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

Question

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?

http://stackoverflow.com/questions/16364187/combining-2-sql-queries-and-getting-result-set-in-one — mohan111, Feb 15 '16 at 12:19

score 1 · Answer 1 · answered Feb 15 '16 at 12:19

1

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;

answered Feb 15 '16 at 12:19

Lamak

69,480
12
108
116

score 1 · Answer 2 · answered Feb 15 '16 at 12:20

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

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

2 Answers2