How to alias ROLLUP in SQL Oracle?-CodePudding

I am creating a pivot table using Oracle SQL where there is a total row at the bottom. It does work however the first column of the row shows as '-' whereas I'd like it to be 'Total' This is my current code:

SELECT
    TEAM,
    COUNT(CASE WHEN status = 'Idea' THEN 1 END) AS "Idea",
    COUNT(CASE WHEN status = 'Published' THEN 1 END) AS "Published",
    COUNT(CASE WHEN status = 'Submitted For Publication' THEN 1 END) AS "Submitted For Publication",
    COUNT(CASE WHEN status = 'In Progress' THEN 1 END) AS "In Progress",
    COUNT(*) AS Total
FROM TEAM_TRACKER
WHERE status IN ('Idea', 'Published', 'Submitted For Publication', 'In Progress')
GROUP BY rollup (TEAM)

How can I achieve this? Thanks!

CodePudding user response：

If the grouping key is not NULLable simple follow the coalesce advice.

More secure and designed for this purpose is to use grouping_id that identifies the total line

Example

create table team_tracker as
(select '1' team from dual union all
 select '1' team from dual union all
 select '2' team from dual union all
 select null team from dual);
 
select 
case when grouping_id(team) = 1 then 'Total' else team end as team,
count(*) cnt 
from team_tracker
group by rollup(team);

TEAM         CNT
----- ----------
1              2
2              1
               1
Total          4

CodePudding user response：

Just use coalesce(Team,'TOTAL') in your select. But this assumes each team can't be null. Though... this type of data edit is best done in the User interface not at the data layer.

DEMO

with CTE AS (SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 2 as amt, 'b' team from dual UNION ALL
SELECT 3 as amt, 'c' team from dual)

SELECT sum(Amt), coalesce(team,'TOTAL')
FROM CTE
GROUP BY rollup (team)

Giving us:

 ---------- ------------------------ 
| SUM(AMT) | COALESCE(TEAM,'TOTAL') |
 ---------- ------------------------ 
|        3 | a                      |
|        2 | b                      |
|        3 | c                      |
|        8 | TOTAL                  |
 ---------- ------------------------

CodePudding user response：

From the answer to your previous question, use GROUPING_ID:

SELECT CASE GROUPING_ID(team)
       WHEN 0
       THEN team
       ELSE 'Total'
       END AS team,
       COUNT(CASE WHEN status = 'Idea' THEN 1 END) AS "Idea",
       COUNT(CASE WHEN status = 'Published' THEN 1 END) AS "Published",
       COUNT(CASE WHEN status = 'Submitted For Publication' THEN 1 END) AS "Submitted For Publication",
       COUNT(CASE WHEN status = 'In Progress' THEN 1 END) AS "In Progress",
       COUNT(*) AS Total
FROM   TEAM_TRACKER
WHERE   status IN ('Idea', 'Published', 'Submitted For Publication', 'In Progress')
GROUP BY ROLLUP(TEAM)