Объединение SQL для добавления итоговой строки для нескольких столбцов

Я использую набор данных о сотрудниках из Kaggle — Dataset

Поверх этого я создал сводную таблицу для ТипаСотрудникаКлассификации и СтатусаСотрудника.

with pivotTable as 
(
    select 
        EmployeeClassificationType, [Active], [Future Start], 
        [Leave of Absence], [Terminated for Cause], 
        [Voluntarily Terminated],
        ([Active] + [Future Start] + [Leave of Absence] + [Terminated for Cause] + [Voluntarily Terminated]) as total
    from 
        (select 
             EmployeeStatus, EmployeeClassificationType, count(*) as total
         from  
             employee_data
         group by 
             EmployeeStatus, EmployeeClassificationType) a
    pivot
        (sum(total)
            for employeeStatus in ([Active], [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated])
        ) as pivotTable
)
-- rollup and find the grand total as well as add a row to total 
select * 
from pivotTable

Вывод выглядит следующим образом:

Я хочу добавить строку для итогов, как показано на этом снимке экрана:

Я пробовал использовать объединение всех столбцов, но, похоже, это не работает.

select 
    coalesce(EmployeeClassificationType, 'Grand Total') as EmployeeClassificationType,
    sum(Active) as Active,
    sum([Future Start]) as [Future Start],
    sum([Leave of Absence]) as [Leave of Absence],
    sum([Terminated for Cause]) as [Terminated for Cause],
    sum([Voluntarily Terminated]) as [Voluntarily Terminated],
    sum(total) as 'Grand Total'
from 
    pivotTable
group by 
    rollup(EmployeeClassificationType, Active, [Future Start], [Leave of Absence], [Terminated for Cause]), [Voluntarily Terminated]

или

select 
    *,
    sum(total) as 'Grand Total'
from 
    pivotTable
group by 
    rollup(EmployeeClassificationType, Active, [Future Start], [Leave of Absence], [Terminated for Cause]), [Voluntarily Terminated], total

🤔 А знаете ли вы, что...
SQL может работать с множеством языковых расширений, таких как PL/SQL, T-SQL и другими.

[РЕШЕНО]Перейти к решению

Sql74185 вопросов Sql server40150 вопросов T sql157 вопросов Group by3434 вопроса Rollup150 вопросов

Ответы:

Решено

данные

CREATE TABLE pivotTable (
   EmployeeClassificationType VARCHAR(90)  
  ,Active                     INTEGER  NOT NULL
  ,FutureStart                INTEGER  NOT NULL
  ,LeaveOfAbsence             INTEGER  NOT NULL
  ,TerminatedForCause         INTEGER  NOT NULL
  ,VoluntarilyTerminated      INTEGER  NOT NULL
  ,total                      INTEGER  NOT NULL
);
INSERT INTO pivotTable 
(EmployeeClassificationType,Active,FutureStart,LeaveOfAbsence,TerminatedForCause,VoluntarilyTerminated,total) VALUES 
('Full-Time',840,26,21,24,102,1013),
('Part-Time',779,20,35,15,105,954),
('Temporary',839,23,30,27,114,1033);

в запросе используйте group by .... with rollup, а не group by rollup(...) и ISNULL, чтобы охватить нулевые значения для столбца «EmployeeClassificationType»

SELECT
ISNULL(EmployeeClassificationType,'TOTAL'),
SUM(Active) Active,
SUM(FutureStart)  FutureStart,
SUM(LeaveOfAbsence)   LeaveOfAbsence,
SUM(TerminatedForCause)  TerminatedForCause,
SUM(VoluntarilyTerminated)  VoluntarilyTerminated,
SUM(total) total
FROM 
pivotTable
 group 
    by EmployeeClassificationType with rollup;

добавьте приведенный выше запрос в свой with CTE

with pivotTable as 
(
    select 
        EmployeeClassificationType, [Active], [Future Start], 
        [Leave of Absence], [Terminated for Cause], 
        [Voluntarily Terminated],
        ([Active] + [Future Start] + [Leave of Absence] + [Terminated for Cause] + [Voluntarily Terminated]) as total
    from 
        (select 
             EmployeeStatus, EmployeeClassificationType, count(*) as total
         from  
             employee_data
         group by 
             EmployeeStatus, EmployeeClassificationType) a
    pivot
        (sum(total)
            for employeeStatus in ([Active], [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated])
        ) as pivotTable
)

SELECT
ISNULL(EmployeeClassificationType,'TOTAL'),
SUM(Active) Active,
SUM(FutureStart)  FutureStart,
SUM(LeaveOfAbsence)   LeaveOfAbsence,
SUM(TerminatedForCause)  TerminatedForCause,
SUM(VoluntarilyTerminated)  VoluntarilyTerminated,
SUM(total) total
FROM 
pivotTable
 group 
    by EmployeeClassificationType with rollup;

dbfiddle

Вместо этого вам следует использовать GROUPING SETS, так как свертывание также даст промежуточное свертывание.

Не используйте '' для разделения имен, используйте только [].

with pivottable as (
-- etc
)
select 
    IIF(GROUPING(EmployeeClassificationType) = 1, 'Grand Total', EmployeeClassificationType) as EmployeeClassificationType,
    sum(Active) as Active,
    sum([Future Start]) as [Future Start],
    sum([Leave of Absence]) as [Leave of Absence],
    sum([Terminated for Cause]) as [Terminated for Cause],
    sum([Voluntarily Terminated]) as [Voluntarily Terminated],
    sum(total) as [Grand Total]
from 
    pivotTable
group by grouping sets
    (EmployeeClassificationType, Active, [Future Start], [Leave of Absence], [Terminated for Cause], [Voluntarily Terminated]),
    ()
);

Объединение SQL для добавления итоговой строки для нескольких столбцов

Ответы:

Интересные вопросы для изучения