Clever Geek Handbook

Renaming SQL NULL for null values ​​and collapsing

I am trying to make a panel to show open tickets and to whom they are assigned, by priority. The dataset looks like this:

|-------------------------|
|  Assigned  |  Priority  |
|-------------------------|
|  JOE       |  Low       |
|  JOE       |  Medium    |
|  MARY      |  High      |
|            |  Medium    |
|  TIM       |  Low       |
|  Mary      |  High      |

The report I am trying to get is:

|---------------------------------------------------------|
|  Employee  |  Low   |   Medium   |   High   |   Total   |
|---------------------------------------------------------|
| Total      |   2    |     2      |    2     |     6     |
| Unassigned |   0    |     1      |    0     |     1     |
|  MARY      |   0    |     0      |    2     |     2     |
|  JOE       |   1    |     1      |    0     |     2     |
|  TIM       |   1    |     0      |    0     |     1     |

I can get everything with this request

SELECT
    CASE WHEN Assigned is null then 'Unassigned' Else Assigned End Employee
    ,   SUM(CASE WHEN Priority = 'Low' THEN 1 ELSE 0 END) AS Low
    ,   SUM(CASE WHEN Priority = 'Medium' THEN 1 ELSE 0 END) AS Medium
    ,   SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS High
    ,   count(Priority) AS Total
FROM table
GROUP BY Assigned WITH ROLLUP
ORDER BY Assigned ASC

This way, however, renames everything that was NULL for Unassigned. It's perfect to give me Unassigned values ​​that don't have anyone in the Assigned field, however my Total column shows Unassigned as well. How do I rename this to Total?

Thanks in advance!

+3


source to share


1 answer


You need to use GROUPING , for example:

SQL Fiddle

SELECT
    CASE WHEN (GROUPING(Assigned) = 1) THEN 'Total'
        When Assigned is null then 'Unassigned' 
        Else Assigned End Employee
    ,   SUM(CASE WHEN Priority = 'Low' THEN 1 ELSE 0 END) AS Low
    ,   SUM(CASE WHEN Priority = 'Medium' THEN 1 ELSE 0 END) AS Medium
    ,   SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS High
    ,   count(Priority) AS Total
FROM table1
GROUP BY Assigned WITH ROLLUP
--DER BY Assigned ASC


Results :

|   EMPLOYEE | LOW | MEDIUM | HIGH | TOTAL |
|------------|-----|--------|------|-------|
| Unassigned |   0 |      1 |    0 |     1 |
|        JOE |   1 |      1 |    0 |     2 |
|       MARY |   0 |      0 |    2 |     2 |
|        TIM |   1 |      0 |    0 |     1 |
|      Total |   2 |      2 |    2 |     6 |
+4


source






More articles:


All Articles