Normalize data with no matter exist or no exist into single SQL query

I have this record in my table TblFinishByClass. Some ID does not attend the Class thus there will have no record exist. There is only 3 class available which are C, TP, and TT.

ID        Class    Month     Year    Finished    Total 
ASAN014    C        04       2020      12         19
ASAN014    TP       04       2020      4          6
ASAN014    TT       04       2020      2          7
ASAN014    C        05       2020      10         18
ASAN014    TP       05       2020      1          2
ASAN014    TT       05       2020      2          6
ASAN015    C        04       2020      21         24
ASAN015    TP       04       2020      0          1
ASAN015    TT       04       2020      8          11
ASAN015    C        05       2020      14         19
ASAN015    TT       05       2020      4          5

As mention early. If ASAN015 does not attend the class for TT, it's data default should be zero for FinishedTT and TotalTT. I need to group it by ID, Month, and Year in which it will look like the following table:

ID        Month    Year   FinishedC TotalC   FinishedTP  TotalTP  FinishedTT  TotalTT
ASAN014     04     2020      12      19          4        6         2          7
ASAN014     05     2020      10      18          1        2         2          6
ASAN015     04     2020      21      24          0        1         8          11
ASAN015     05     2020      14      19          4        5         0          0

My question is could we do this in one query?

1 answer

  • answered 2020-05-22 13:00 Eric Brandt

    You can use conditional aggregation.

    SELECT
        ID,
        Month,
        Year,
        SUM(CASE WHEN Class = 'C' THEN Finished ELSE 0 END) AS FinishedC,
        SUM(CASE WHEN Class = 'C' THEN Total ELSE 0 END) AS TotalC,
        SUM(CASE WHEN Class = 'TT' THEN Finished ELSE 0 END) AS FinishedTT,
        SUM(CASE WHEN Class = 'TT' THEN Total ELSE 0 END) AS TotalTT,
        SUM(CASE WHEN Class = 'TP' THEN Finished ELSE 0 END) AS FinishedTP,
        SUM(CASE WHEN Class = 'TP' THEN Total ELSE 0 END) AS TotalTP
    FROM 
     TblFinishByClass
    GROUP BY
        ID,
        Month,
        Year;
    

    Results:

    +----+---------+-------+------+-----------+--------+------------+---------+------------+---------+
    |    |   ID    | Month | Year | FinishedC | TotalC | FinishedTT | TotalTT | FinishedTP | TotalTP |
    +----+---------+-------+------+-----------+--------+------------+---------+------------+---------+
    |  1 | ASAN014 |     4 | 2020 |        12 |     19 |          2 |       7 |          4 |       6 |
    |  2 | ASAN014 |     5 | 2020 |        10 |     18 |          2 |       6 |          1 |       2 |
    |  3 | ASAN015 |     4 | 2020 |        21 |     24 |          8 |      11 |          0 |       1 |
    |  4 | ASAN015 |     5 | 2020 |        14 |     19 |          4 |       5 |          0 |       0 |
    +----+---------+-------+------+-----------+--------+------------+---------+------------+---------+