Imran Ali Khan Imran Ali Khan - 25 days ago 7
SQL Question

How to count and sum in sql server

I have following table T1

ID Type Amount
1 1 100
2 2 100
3 2 100
4 2 100
5 1 100
6 1 100
7 2 100
8 1 100
9 1 100


I need result as follows

TotalRecods Type1Recods type2Recods Type1Amount type2Amount
9 5 4 500 400


I am trying

SELECT COUNT(id) AS TotalRecords ,
COUNT(type) AS Type1Records,
COUNT(type) AS Type2Records,
SUM(Amount ) AS Type1Amount,
SUM(Amount) AS Type2Amount
FROM T1


its returning

TotalRecords Type1Records Type2Records Type1Amount Type2Amount
9 9 9 900 900


I dont understand how to filter it on type. may be a simple step I am missing.

Answer Source
SELECT
        COUNT(*) [TotalRecords],
        COUNT(CASE WHEN [Type] = 1 THEN 1 END) [Type1Records],
        COUNT(CASE WHEN [Type] = 2 THEN 1 END) [Type2Records],
        SUM(CASE WHEN [Type] = 1 THEN [Amount] END) [Type1Amount],
        SUM(CASE WHEN [Type] = 2 THEN [Amount] END) [Type2Amount]
    FROM
        [t];

Just note that if there is no record for some type, COUNT will return 0, but result from SUM will NULL. If you want 0 result also from SUM function, you can use it like:

SUM(CASE WHEN [Type] = 1 THEN [Amount] ELSE 0 END) [Type1Amount]