grouping total

Question

Here is sample data,

create table #test (Region varchar(15),Country varchar(15),Level1 varchar(15),Level2 varchar(15),Sales int)
 
insert into #test select 'Americas','US',    'South',    'Corp',    1222
insert into #test select 'Americas','US',    'South',    'Corp',    1300
insert into #test select 'Americas','US',    'South',    'Corp1',    2300
insert into #test select 'Americas','US',    'South',    'Corp1'    ,3200
insert into #test select 'Asia','India',    'Central',    'LVL',    200
insert into #test select 'Asia','India',    'Central',    'LVL',    30
insert into #test select 'Asia','India',    'Central',    'LVL',    230
insert into #test select 'Europe','UK',    'Central',    'LVL1',    430
insert into #test select 'Europe','UK',    'Central',    'LVL2',    520

Americas    US        South     Corp    1222
Americas    US        South     Corp    1300
                                         SubTot   5522
Americas    US        South    Corp1    2300
Americas    US        South    Corp1    3200
                                           SubTot   5500
Total for Americas                           11022                          
                          
Asia        India    Central       LVL        200
Asia        India    Central       LVL          30
Asia        India    Central       LVL        230
                                            SubTot   460
Total for Asia                                      460                          

Europe        UK        Central    LVL1    430
                                             SubTot   430

Europe        UK        Central    LVL2    520
                                            SubTot   520
Total for Europe                                940                          

how to get total?

Thanks

V

Answer 1

Take a look at the ROLLUP clause in the GROUP BY topic in BOL. See also GROUPING function.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

i tired, getting confused with  with group rollup  / cube, can i get sample query for above example

Answer 3

A bit tricky with your data, but you can use this as a starting point:

CREATE TABLE #test (Region VARCHAR(15),country VARCHAR(15),Level1 VARCHAR(15),Level2 VARCHAR(15),Sales INT)
 
INSERT INTO #test SELECT 'Americas','US',    'South',    'Corp',    1222
INSERT INTO #test SELECT 'Americas','US',    'South',    'Corp',    1300
INSERT INTO #test SELECT 'Americas','US',    'South',    'Corp1',    2300
INSERT INTO #test SELECT 'Americas','US',    'South',    'Corp1'    ,3200
INSERT INTO #test SELECT 'Asia','India',    'Central',    'LVL',    200
INSERT INTO #test SELECT 'Asia','India',    'Central',    'LVL',    30
INSERT INTO #test SELECT 'Asia','India',    'Central',    'LVL',    230
INSERT INTO #test SELECT 'Europe','UK',    'Central',    'LVL1',    430
INSERT INTO #test SELECT 'Europe','UK',    'Central',    'LVL2',    520

INSERT INTO #test SELECT 'Europe','Belgium',    'Central',    'LVL1',    130
INSERT INTO #test SELECT 'Europe','Belgium',    'Central',    'LVL2',    620

SELECT ISNULL(Region,
    CASE WHEN grouping(Region) = 0 THEN 'UNKNOWN' ELSE 'Grand Total' END)
    AS Region
    ,ISNULL(cast(country as varchar(40)),
    CASE WHEN grouping(country)= 0 THEN 'UNKNOWN' ELSE 
    case when grouping(region) = 0 then 'Total for ' + Region else '' END 
    end)
    AS country
    ,ISNULL(cast(Level1 as varchar(40)),
    CASE WHEN grouping(Level1) = 0 THEN 'UNKNOWN' ELSE 
    case when GROUPING(Country) = 1 then '' else 'Sub Total'  end END)
    AS Level1, 
    ISNULL(Level2, CASE WHEN grouping(Level2) = 0 THEN 'UNKNOWN' ELSE 
    case when GROUPING(level2) = 1 then '' else   'SubTot' END end )
    AS Level2, SUM(Sales) AS Sales
FROM #test T
GROUP BY ROLLUP(Region, country, Level1, Level2)

ORDER BY Grouping(Region),Region,
GROUPING(Country), country, 
GROUPING(Level1),Level1, 
Grouping(Level2), Level2    

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

I work around with your query,

 

Americas    US        South     Corp    1222
Americas    US        South     Corp    1300
                                         SubTot   5522

Need to two rows but Corp is getting combined,  i want to get two rows

Answer 5

You know what, it's easier to forget about GROUP BY and do a standard UNION ALL:

select Region, country, Level1, Level2, Sales from (select *, 
cast(Region as CHAR(16)) + CAST(country as CHAR(16)) + CAST(Level1 as CHAR(16)) + CAST(Level2 as CHAR(16)) 

 as Grp from #test 
UNION ALL select Region, Country, Level1, 'SubTot' as Level2,SUM(sales) as Sales, 
cast(Region as CHAR(16)) + CAST(country as CHAR(16)) + CAST(Level1 as CHAR(16)) + CAST(Level2 as CHAR(16)) + REPLICATE('z',16) as Grp 
from #test group by Region, country, Level1, Level2

UNION ALL select Region, Country, 'Sub Total', '' as Level2,SUM(sales) as Sales, 
cast(Region as CHAR(16)) + CAST(country as CHAR(16)) + CAST(Level1 as CHAR(16)) + REPLICATE('z',16) as Grp 
from #test group by Region, country, Level1

UNION ALL select Region, Country + ' Total:', '', '' as Level2,SUM(sales) as Sales, 
cast(Region as CHAR(16)) + CAST(country as CHAR(16)) + REPLICATE('z',16) + REPLICATE('z',16) as Grp  
from #test group by Region, country

UNION ALL select Region, 'Total for ' + Region, '', '' as Level2,SUM(sales) as Sales, 

cast(Region as CHAR(16)) + REPLICATE('z',16) + REPLICATE('z',16) + REPLICATE('z',16) as Grp  
from #test group by Region

UNION ALL select 'Grand Total' as Region, '' as Country, '', '' as Level2,SUM(sales) as Sales, 
REPLICATE('z',16)
from #test ) X
ORDER BY Grp

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog