Вычисление промежуточных итогов с помощью свертки в SQL Server

У меня есть таблица (#mytable), содержащая основную финансовую информацию о компаниях.

CREATE TABLE #mytable 
(
        Companyid varchar2(50),
        DataDescription varchar2(100),
        Value DECIMAL(23,6),
        Department varchar2(100),
        CurrencyIS03 varchar2(5),
        DateofData datetime
)

INSERT INTO #mytable (Companyid, DataDescription, Value, Department, CurrencyIS03, DateofData)
    SELECT 
        'A100', 'Revenue', '1000.00', 'Corporate', 'USD', '2014-12-31 00:00:00' 
    UNION ALL
    SELECT 'A100','Revenue','2000.00','Banking','USD','2014-12-31 00:00:00'     
    UNION ALL
    SELECT 'A100','Revenue','2500.00','Corporate','USD','2013-12-31 00:00:00' 
    UNION ALL
    SELECT 'A100','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00' 
    UNION ALL
    SELECT 'A100','Operating Income','10000.00','Corporate','USD','2014-12-31 00:00:00' 
    UNION ALL
    SELECT 'A100','Operating Income','1000.00','Banking','USD','2014-12-31 00:00:00' 
    UNION ALL
    SELECT 'A200','Revenue','1100.00','Corporate','USD','2013-12-31 00:00:00' 
    UNION ALL
    SELECT 'A200','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00' 
    UNION ALL
    SELECT 'A200','Operating Income','5500.00','Corporate','USD','2014-12-31 00:00:00' 
    UNION ALL
    SELECT 'A200','Operating Income','10000.00','Banking','USD','2014-12-31 00:00:00'

Мне нужно найти промежуточные итоги на основе Companyid, DataDescription, Department, CurrencyIS03, DateofData. Я не уверен, как это сделать. Я попытался сделать следующее

select 
    Companyid, DataDescription,
    sum(Value) as total,
    Department, CurrencyIS03, DateofData 
from 
    #mytable
group by 
    rollup(CompanyID, Datadescription, Department, CurrencyIS03, DateofData)

Это не возвращает правильный ответ.

Ниже то, что я ожидаю.

CREATE TABLE #outputtable 
(
        Companyid varchar2(50),
        DataDescription varchar2(100),
        TotalValue DECIMAL(23,6),
        Department varchar2(100),
        CurrencyIS03 varchar2(5),
        DateofData datetime
)

INSERT INTO #outputtable (Companyid, DataDescription, TotalValue, Department, CurrencyIS03, DateofData)
 SELECT 'A100','Revenue','1000.00','Corporate','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A100','Revenue','2000.00','Banking','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A100','Revenue','3000.00','Total','USD','2014-12-31 00:00:00' UNION ALL

 SELECT 'A100','Revenue','2500.00','Corporate','USD','2013-12-31 00:00:00' UNION ALL
 SELECT 'A100','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00' UNION ALL
 SELECT 'A100','Revenue','5500.00','Total','USD','2013-12-31 00:00:00' UNION ALL

 SELECT 'A100','Operating Income','10000.00','Corporate','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A100','Operating Income','1000.00','Banking','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A100','Operating Income','11000.00','Total','USD','2014-12-31 00:00:00' UNION ALL

 SELECT 'A200','Revenue','1100.00','Corporate','USD','2013-12-31 00:00:00' UNION ALL
 SELECT 'A200','Revenue','3000.00','Banking','USD','2013-12-31 00:00:00' UNION ALL
 SELECT 'A200','Revenue','4100.00','Total','USD','2013-12-31 00:00:00' UNION ALL

 SELECT 'A200','Operating Income','5500.00','Corporate','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A200','Operating Income','10000.00','Banking','USD','2014-12-31 00:00:00' UNION ALL
 SELECT 'A200','Operating Income','15500.00','Total','USD','2014-12-31 00:00:00'

Любая помощь приветствуется.

Спасибо


sql-server rollup
person user2726975    schedule 14.01.2016    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Похоже, вы только сворачиваете отдел внутри группы, поэтому вы просто используете ROLLUP(Department) и группируете остальные в обычном режиме.

SELECT  Companyid,
        DataDescription,
        SUM(Value) AS total,
        COALESCE(Department,'Total') Department,
        CurrencyIS03,
        DateofData
FROM    #mytable
GROUP BY CompanyID,
        Datadescription,
        ROLLUP(Department),
        CurrencyIS03,
        DateofData
ORDER BY CompanyID ASC,
        Datadescription DESC,
        DateofData DESC,
        Department ASC
person JamieD77    schedule 14.01.2016

arrow_upward
0
arrow_downward

Я не уверен, почему вы хотите использовать ROLLUP для этого.

Я бы просто сделал:

select * from 
(select Companyid,DataDescription,sum(Value) as Value ,'Total' as Department,CurrencyIS03,DateofData 
 from mytable
 group by CompanyID, Datadescription, CurrencyIS03,DateofData
 union all
 select * from mytable) a
 order by  CompanyID asc, Datadescription desc, DateofData desc, Department asc

Существует скрипка

person HubertL    schedule 14.01.2016