How To Create Total Row and Total Column to Pivot Table in SQL-CodePudding

I have created a pivot table using SQL but want to add column and row totals. How am I able to achieve this using SQL. This is the following code I have for my pivot table.

SELECT * from 
(
    select committee, status, id from EMPLOYEES
) piv

pivot (
count (id)
for status in ('Resume Review', 'Interviewing', 'Coding Challenge', 'Hired')
) pivot_table;

My Current Table:

Committee   Resume Review   Interviewing    Take Home Challenge  
UI/UX              3             2                  1              
Finance            0             2                  2              
Marketing          2             4                  1

Desired Table:

Committee   Resume Review   Interviewing    Take Home Challenge  Total 
UI/UX              3             2                  1              6
Finance            0             2                  2              4
Marketing          2             4                  1              7
Total              5             8                  4              17

CodePudding user response：

If you are open to not using the PIVOT operator, we can use conditional aggregation to achieve your result:

SELECT
    Committee,
    COUNT(CASE WHEN status = 'Resume Review'    THEN 1 END) AS "Resume Review",
    COUNT(CASE WHEN status = 'Interviewing'     THEN 1 END) AS "Interviewing",
    COUNT(CASE WHEN status = 'Coding Challenge' THEN 1 END) AS "Coding Challenge",
    COUNT(*) AS Total
FROM EMPLOYEES
WHERE status IN ('Resume Review', 'Interviewing', 'Coding Challenge')
GROUP BY Committee;

CodePudding user response：

use this

SELECT * from 
(
    select committee, status, id from EMPLOYEES
) piv

pivot (
count (id)
for status in ('Resume Review', 'Interviewing', 'Coding Challenge', 'Hired')
) pivot_table;

union all 

SELECT 'Total' as committee,
sum(Resume Review) as Resume Review,
sum(Interviewing) as Interviewing,
sum(Coding Challenge) as Coding Challenge,
sum(Hired) as Hired,
sum(Resume Review)   sum(Interviewing)  sum(Coding Challenge)   sum(Hired) as Total 
from 
(
    select committee, status, id from EMPLOYEES
) piv

pivot (
count (id)
for status in ('Resume Review', 'Interviewing', 'Coding Challenge', 'Hired')
) pivot_table;