CODEWITHSUNDEEP
sql-serverrollup
user2726975
user2726975

Reputation: 1353

Calculating Subtotals using rollup in SQL Server

I have a table(#mytable) that contains basic financial info about companies.

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'

I have to find sub totals based on Companyid, DataDescription, Department, CurrencyIS03, DateofData. I am not sure how to do that. I tried doing the following

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

This is not returning the correct answer.

Below is what I expect.

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'

Any help is appreciated.

Thanks

Upvotes: 1

Views: 62

Answers (2)

JamieD77
JamieD77

Reputation: 13949

It looks like you're only rolling up Department within the grouping so you just have use ROLLUP(Department) and group by the rest normally.

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

Upvotes: 1

HubertL
HubertL

Reputation: 19544

I'm not sure why you would want to use ROLLUP for this.

I would just do:

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

There is a fiddle

Upvotes: 0

Related Questions