CODEWITHSUNDEEP
sqlsql-server
Krypt
Krypt

Reputation: 421

SQL Aggregation depending on value of attribute in unselected column

I've got a table TABLE1 like this:

|--------------|--------------|--------------|
|      POS     |     UNIT     |    VOLUME    |
|--------------|--------------|--------------|
|       1      |      M2      |     20       |
|       1      |      M2      |     30       |
|       1      |      M3      |     40       |
|       2      |      M2      |     100      |
|       2      |      M3      |     20       |
|       3      |      ST      |     30       |
|       3      |      M2      |     10       |
|--------------|--------------|--------------|

Depending on the value of the column UNIT I want to aggregate as follows (each UNIT becomes a new column with the sum of the according value):

|--------------|--------------|--------------|--------------|
|      POS     |   VOLUME_M2  |   VOLUME_M3  |   VOLUME_ST  |
|--------------|--------------|--------------|--------------|
|       1      |      50      |      40      |       0      |
|       2      |      100     |      20      |       0      |
|       3      |      10      |      0       |      30      |
|--------------|--------------|--------------|--------------|

My Solution is

SELECT POS,
       CASE
            WHEN UNIT = 'M2' 
                THEN SUM(VOLUME)
                ELSE 0
            END AS VOLUME_M2,
       CASE
            WHEN UNIT = 'M3' 
                THEN SUM(VOLUME)
                ELSE 0
            END AS VOLUME_M3,
       CASE
            WHEN UNIT = 'ST' 
                THEN SUM(VOLUME)
                ELSE 0
            END AS VOLUME_S
FROM TABLE1
GROUP BY POS, UNIT

My problem is, that my code does not work if I leave out UNIT in the GROUP BY statement (I either have to use it in my aggregation or in my GROUP BY statement)

Therefore I get something like this:

|--------------|--------------|--------------|--------------|
|      POS     |   VOLUME_M2  |   VOLUME_M3  |   VOLUME_ST  |
|--------------|--------------|--------------|--------------|
|       1      |      50      |      0       |       0      |
|       1      |      0       |      40      |       0      |
|       2      |      0       |      20      |       0      |
|       2      |      100     |      0       |       0      |
|       3      |      10      |      0       |       0      |
|       3      |      0       |      0       |      30      |
|--------------|--------------|--------------|--------------|

Besides, could anyone give me a hint, how it is possible to automatically get this type of result (especially if there are a lot of values for UNIT).

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Close. For conditional aggregation, the case expression is an argument to the aggregation function:

SELECT POS,
       SUM(CASE WHEN UNIT = 'M2' THEN VOLUME ELSE 0 END) AS VOLUME_M2,
       SUM(CASE WHEN UNIT = 'M3' THEN VOLUME ELSE 0 END) AS VOLUME_M3,
       SUM(CASE WHEN UNIT = 'ST' THEN VOLUME ELSE 0 END) AS VOLUME_ST
FROM TABLE1
GROUP BY POS;

Upvotes: 2

Related Questions