Search code examples
sqlsum

How to sum() if missing data in same column in SQL

I need to sum up the quantity according to typein the table below.

Table
Name Quantity Type 
a       6      AB
b       2      BB
b       4      AB
c       8      BB
a       3      BB
b       5      AB

Outcome
Name  AB_Type  BB_Type
a       6      3
b       9      2
c       0      8

I am trying the below query but I can't get the numbers right.

SELECT S.Name, SUM(S1.Quantity) AS AB_Type, SUM(S2.Quantity) AS BB_Type
FROM Table AS S, Table AS S1, Table AS S2
WHERE S.Name = S1.SName = S2.Name AND S1.Type = 'AB'AND S2.Type = 'BB' 
GROUP BY S.Name;

Thanks in advance!!

Solution

  • Try this

    SELECT 
    Name, 
    SUM(CASE WHEN Type = 'AB' THEN Quantity ELSE 0 END) AS AB_Type, 
    SUM(CASE WHEN Type = 'BB' THEN Quantity ELSE 0 END) AS BB_Type
FROM Table
GROUP BY Name