Aggregate character based on value MODE/FIRST/LAST within GROUP BY / PARTITION BY in Azure SQL Database (SQL Server)

Question

In an Azure SQL Server database, I want to create a few aggregations that take the MODE/FIRST/LAST element by a measure.

My table looks like this:

+----+-------+-------+---------+
| PK | Char1 | Char2 | Measure |
+----+-------+-------+---------+
|  1 | A     | ch_k  |       1 |
|  1 | B     | ch_m  |       2 |
|  1 | B     | ch_n  |       3 |
|  2 | A     | ch_k  |       1 |
|  2 | A     | ch_j  |       3 |
|  2 | B     | ch_n  |       2 |
|  2 | C     | ch_l  |       5 |
+----+-------+-------+---------+

I would like to obtain this:

+----+--------+--------+---------+-------+-------+-------+
| PK | mode_A | mode_B | mode_AB | sum_A | sum_B | total |
+----+--------+--------+---------+-------+-------+-------+
|  1 | ch_k   | ch_n   | ch_n    |     1 |     5 |     6 |
|  2 | ch_j   | ch_n   | ch_j    |     4 |     2 |    11 |
+----+--------+--------+---------+-------+-------+-------+

I can easily obtain sum_A and sum_B by:

SELECT 
    PK,
    SUM(CASE 
           WHEN Char1 = 'A' THEN Measure
           ELSE 0
        END) AS sum_A,
    SUM(CASE 
           WHEN Char1 = 'B' THEN Measure
           ELSE 0
        END) AS sum_B
FROM
    TABLE
GROUP BY 
    PK

I struggle to find an efficient way to obtain mode_A, mode_B and mode_AB. I have found two questions that are closely related question1 and question2. However, I cannot apply them to my case.

I believe that I can create individual tables for each mode and join them back but it doesn't seem too efficient/elegant/generalisable. Also, I have thought using a GROUP BY PK , Char1 , PARTITION BY PK and GROUP BY PK afterwards, but it looks a bit cumbersome.

Yes, what do you mean by these "modes," exactly? And shouldn't `mode_B` for `PK` 1 be `ch_m`? Why is it `ch_n` as shown in your example? — robbpriestley, Dec 08 '19 at 02:11
mode_AB is the `Char2` with a highest `Measure` value when `Char1` is 'A' or 'B'. The answer by @Vignesh does the job. — Jon Nagra, Dec 08 '19 at 17:04

Vignesh Kumar A · Accepted Answer · 2019-12-08T07:06:30.743

Until or unless knowing the logic to frame Mode_AB, it will be difficult to use the right logic.

Try this query

DB FIDDLE

SELECT PK,MIN(Mode_A) Mode_A, MAX(Mode_B) Mode_B,
MIN(CASE WHEN Acnt > 1 THEN Mode_A ELSE Mode_B END) Mode_AB,
MIN(sum_A)sum_A,MIN(sum_B)sum_B,MIN(Total)Total
FROM (
  SELECT DISTINCT 
    PK, 
    CASE WHEN Char1 = 'A' THEN Char2 Else Null END 'Mode_A',
    CASE WHEN Char1 = 'B' THEN Char2 Else Null END 'Mode_B',
    COUNT(CASE WHEN Char1 = 'A' THEN Char2 Else Null END) OVER(PARTITION BY PK ORDER BY PK) Acnt,
    COUNT(CASE WHEN Char1 = 'B' THEN Char2 Else Null END) OVER(PARTITION BY PK ORDER BY PK) Bcnt,
    SUM(CASE WHEN Char1 = 'A' THEN Measure ELSE 0 END) OVER(PARTITION BY PK ORDER BY PK) AS sum_A,
    SUM(CASE WHEN Char1 = 'B' THEN Measure ELSE 0 END) OVER(PARTITION BY PK ORDER BY PK)AS sum_B,
    SUM(Measure) OVER(PARTITION BY PK ORDER BY PK) AS Total
  FROM Tbl
) X
GROUP BY PK;

`mode_AB` is not exactly that but I can tweak it to do the job. Thanks a lot. — Jon Nagra, Dec 08 '19 at 17:04

Aggregate character based on value MODE/FIRST/LAST within GROUP BY / PARTITION BY in Azure SQL Database (SQL Server)

1 Answers1