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

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!!

CodePudding user response：

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

CodePudding user response：

you can use pivot as follows:

SELECT Name,
iif(AB_Type is null,0,AB_Type) AB_Type,
iif(BB_Type is null,0,BB_Type) BB_Type
FROM (
  SELECT 
    Name
    ,Quantity
   ,CONCAT(Type, '_type') AS Col
  FROM table
) Src
PIVOT (
  sum(Quantity) 
  FOR Col IN (
    [AB_Type], [BB_Type]
  ) 
) Pvt