Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql query help eith grouping sets

Author  Topic 

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-22 : 13:37:34
Hi All,

I have this data in my database:


pk fiscalyear_title fiscal_data1 fiscal_data2 fiscal_data3 fiscal_total
7 US.TEST 12.234 12.456 89.786 114.476
7 US.TEST1 45.987 45.678 34.987 126.652
7 US.TEST3 67.456 34.234 98.567 200.257
7 GE.TEST1 678.987 34.456 34.567 748.010
7 GE.TEST2 98.567 66.789 55.786 221.142
7 NL.TEST1 67.567 34.567 98.789 200.923
7 NL.TEST2 33.567 34.789 33.567 101.923
7 NL.TEST3 99.678 44.678 34.567 178.923
7* Grand Total Costs 1104.043 307.647 480.616 1892.306


I want something like this from above table:


pk fiscalyear_title fiscal_data1 fiscal_data2 fiscal_data3 fiscal_total
7 US.TEST 12.234 12.456 89.786 114.476
7 US.TEST1 45.987 45.678 34.987 126.652
7 US.TEST3 67.456 34.234 98.567 200.257
US. Total Costs 125.677 92.368 223.34 441.385

7 GE.TEST1 678.987 34.456 34.567 748.010
7 GE.TEST2 98.567 66.789 55.786 221.142
GE. Total Costs 777.554 101.245 90.353 969.152

7 NL.TEST1 67.567 34.567 98.789 200.923
7 NL.TEST2 33.567 34.789 33.567 101.923
7 NL.TEST3 99.678 44.678 34.567 178.923
NL.Total Cost 200.812 114.034 167.923 481.769


7* Grand Total Costs 1104.043 307.647 480.616 1892.306


I want total based on each title so

for US, iwant the total and then for GE, I want another total and same thing for NL calaculated at the bottm of each fiscalTitle.


I tried writing this query this way



SELECT FISCALYEAR_TITLE, SUM(FISCAL_DATA1), SUM(FISCAL_DATA2), SUM(FISCAL_DATA3), SUM(FISCAL_TOTAL)

FROM myTable

GROUP BY GROUPING SETS
FISCALYEAR_TITLE

but this doesn't give me the required result.

any help will be appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-22 : 16:13:06
This type of thing should be done in a report builder like SSRS. but if you need a sql solution here's one way:

;with yourTable (pk, fiscalyear_title, fiscal_data1, fiscal_data2, fiscal_data3, fiscal_total)
as
(
select 7 ,'US.TEST' ,12.234 ,12.456 ,89.786 ,114.476 union all
select 7 ,'US.TEST1' ,45.987 ,45.678 ,34.987 ,126.652 union all
select 7 ,'US.TEST3' ,67.456 ,34.234 ,98.567 ,200.257 union all
select 7 ,'GE.TEST1' ,678.987 ,34.456 ,34.567 ,748.010 union all
select 7 ,'GE.TEST2' ,98.567 ,66.789 ,55.786 ,221.142 union all
select 7 ,'NL.TEST1' ,67.567 ,34.567 ,98.789 ,200.923 union all
select 7 ,'NL.TEST2' ,33.567 ,34.789 ,33.567 ,101.923 union all
select 7 ,'NL.TEST3' ,99.678 ,44.678 ,34.567 ,178.923 union all
select 7 ,'Grand Total Costs' ,1104.043 ,307.647 ,480.616 ,1892.306
)

select pk
,coalesce(fiscalyear_title, grp) as fiscalyear_titel
, fiscal_data1
, fiscal_data2
, fiscal_data3
, fiscal_total
from (
select pk
, case when fiscalyear_title = 'Grand Total Costs' then 'zzz' else left(fiscalyear_title,3) end as grp
, fiscalyear_title
, fiscal_data1
, fiscal_data2
, fiscal_data3
, fiscal_total
, 0 as srt
from yourTable
union all
select min(pk) pk
,left(fiscalyear_title,3) + ' total costs' grp
,null
,SUM(FISCAL_DATA1) fiscal_data1
, SUM(FISCAL_DATA2) fiscal_data2
, SUM(FISCAL_DATA3) fiscal_data3
, SUM(FISCAL_TOTAL) FISCAL_TOTAL
,1 as srt
from yourTable
where fiscalyear_title != 'Grand Total costs'
group by left(fiscalyear_title,3)
) d
order by grp, srt, fiscalyear_title

OUTPUT:

pk fiscalyear_titel fiscal_data1 fiscal_data2 fiscal_data3 fiscal_total
----------- ----------------- ---------------------------------------------------------------------
7 GE.TEST1 678.987 34.456 34.567 748.010
7 GE.TEST2 98.567 66.789 55.786 221.142
7 GE. total costs 777.554 101.245 90.353 969.152
7 NL.TEST1 67.567 34.567 98.789 200.923
7 NL.TEST2 33.567 34.789 33.567 101.923
7 NL.TEST3 99.678 44.678 34.567 178.923
7 NL. total costs 200.812 114.034 166.923 481.769
7 US.TEST 12.234 12.456 89.786 114.476
7 US.TEST1 45.987 45.678 34.987 126.652
7 US.TEST3 67.456 34.234 98.567 200.257
7 US. total costs 125.677 92.368 223.340 441.385
7 Grand Total Costs 1104.043 307.647 480.616 1892.306



Be One with the Optimizer
TG
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-23 : 01:16:21
It's not a best practice to go for 'UNION ALL' all the time..
If your data is limited then go for it....





CREATE TABLE #Tab (pk VARCHAR(20),fiscalyear_title VARCHAR(100),fiscal_data1 FLOAT,fiscal_data2 FLOAT,fiscal_data3 FLOAT,fiscal_total FLOAT)
INSERT INTO #tab VALUES
('7','US.TEST1',12.234,12.456,89.786,114.476)
,('7','US.TEST1', 45.987,45.678,34.987,126.652)
,('7','US.TES3', 67.456,34.234,98.567,200.257)
,('7','GE.TEST1', 678.987,34.456,34.567,748.010)
,('7','GE.TEST2', 98.567,66.789,55.786,221.142)
,('7','NL.TEST1',67.567,34.567,98.789,200.923)
,('7','NL.TEST2', 33.567,34.789,33.567,101.923)
,('7','NL.TEST3', 99.678,44.678,34.567,178.923)
,('7 *','Grand Total Costs',1104.043,307.647,480.616,1892.306)


SELECT * FROM #tab WHERE fiscalyear_title like('US%')
UNION ALL
SELECT DISTINCT REPLACE(7,pk,' ') ,'US. Total Costs ' AS fiscalyear_title
, SUM(fiscal_data1) AS fiscal_data1
, SUM(fiscal_data2) AS fiscal_data2
, SUM(fiscal_data3) AS fiscal_data3
, SUM(fiscal_total)al_total FROM #Tab WHERE fiscalyear_title like('US%') GROUP BY pk
UNION ALL
SELECT * FROM #tab WHERE fiscalyear_title like('GE%')
UNION ALL
SELECT DISTINCT REPLACE(7,pk,' ') ,'GE. Total Costs ' AS fiscalyear_title
, SUM(fiscal_data1) AS fiscal_data1
, SUM(fiscal_data2) AS fiscal_data2
, SUM(fiscal_data3) AS fiscal_data3
, SUM(fiscal_total)al_total FROM #Tab WHERE fiscalyear_title like('GE%') GROUP BY pk
UNION ALL
SELECT * FROM #tab WHERE fiscalyear_title like('NL%')
UNION ALL
SELECT DISTINCT REPLACE(7,pk,' ') ,'NL. Total Costs ' AS fiscalyear_title
, SUM(fiscal_data1) AS fiscal_data1
, SUM(fiscal_data2) AS fiscal_data2
, SUM(fiscal_data3) AS fiscal_data3
, SUM(fiscal_total)al_total FROM #Tab WHERE fiscalyear_title like('NL%') GROUP BY pk
UNION ALL
SELECT * FROM #tab WHERE fiscalyear_title like('Grand%')




---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-04-23 : 22:22:24
other alternative

WITH CTE (pk, fiscalyear_title, fiscal_data1, fiscal_data2, fiscal_data3, fiscal_total) AS(
select 7 ,'US.TEST' ,12.234 ,12.456 ,89.786 ,114.476 union all
select 7 ,'US.TEST1' ,45.987 ,45.678 ,34.987 ,126.652 union all
select 7 ,'US.TEST3' ,67.456 ,34.234 ,98.567 ,200.257 union all
select 7 ,'GE.TEST1' ,678.987 ,34.456 ,34.567 ,748.010 union all
select 7 ,'GE.TEST2' ,98.567 ,66.789 ,55.786 ,221.142 union all
select 7 ,'NL.TEST1' ,67.567 ,34.567 ,98.789 ,200.923 union all
select 7 ,'NL.TEST2' ,33.567 ,34.789 ,33.567 ,101.923 union all
select 7 ,'NL.TEST3' ,99.678 ,44.678 ,34.567 ,178.923 union all
select 7 ,'Grand Total Costs',1104.043 ,307.647 ,480.616 ,1892.306
)
SELECT
pk,
CASE WHEN (GROUPING(fiscalyear_title) = 1) THEN grp + '.Total Costs'
ELSE ISNULL(fiscalyear_title, 'UNKNOWN') END fiscalyear_title,
SUM(fiscal_data1),
SUM(fiscal_data2),
SUM(fiscal_data3),
SUM(fiscal_total)
FROM (
SELECT
COALESCE(PARSENAME(fiscalyear_title, 2), fiscalyear_title)grp,
*
FROM CTE
)src
GROUP BY PK, ROLLUP(grp, fiscalyear_title)
HAVING GROUPING(grp) <> 1
AND NOT (grp = 'Grand Total Costs' AND GROUPING(fiscalyear_title) = 1)
ORDER BY CASE WHEN fiscalyear_title = 'Grand Total Costs' THEN 1 ELSE -1 END
Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2014-04-24 : 14:28:46
Thank you all for your help.
Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2014-05-08 : 14:21:38
In the above query, is it possible to put US at the top and then other countries come after that. Right now the query is ordered, but user always wants US at the top and then other countries.
Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2014-05-08 : 16:50:01
I cannot hard code the countries like this because there can be lot of different countries.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 17:50:18
quote:
Originally posted by anjali5

In the above query, is it possible to put US at the top and then other countries come after that. Right now the query is ordered, but user always wants US at the top and then other countries.



ORDER BY CASE WHEN fiscalyear_title LIKE 'US%' THEN -1
WHEN fiscalyear_title = 'Grand Total Costs' THEN 1 ELSE 0 END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-09 : 05:08:46
Try...


CREATE TABLE Tab (pk VARCHAR(20),fiscalyear_title VARCHAR(100),fiscal_data1 FLOAT,fiscal_data2 FLOAT,fiscal_data3 FLOAT,fiscal_total FLOAT)
INSERT INTO Tab VALUES
('7','US.TEST1',12.234,12.456,89.786,114.476)
,('7','US.TEST1', 45.987,45.678,34.987,126.652)
,('7','US.TES3', 67.456,34.234,98.567,200.257)
,('7','GE.TEST1', 678.987,34.456,34.567,748.010)
,('7','GE.TEST2', 98.567,66.789,55.786,221.142)
,('7','NL.TEST1',67.567,34.567,98.789,200.923)
,('7','NL.TEST2', 33.567,34.789,33.567,101.923)
,('7','NL.TEST3', 99.678,44.678,34.567,178.923)
,('7 *','Grand Total Costs',1104.043,307.647,480.616,1892.306)
SELECT pk
,fiscalyear_title
,fiscal_data1
,fiscal_data2
,fiscal_data3
,FISCAL_TOTAL
FROM (
SELECT *,1 AS SORT FROM tab
UNION ALL
SELECT ' ' pk
,left(fiscalyear_title,3) + ' total costs' fiscalyear_title
,SUM(FISCAL_DATA1) fiscal_data1
,SUM(FISCAL_DATA2) fiscal_data2
,SUM(FISCAL_DATA3) fiscal_data3
,SUM(FISCAL_TOTAL) FISCAL_TOTAL
,2 AS Sort
FROM Tab
WHERE fiscalyear_title <> 'Grand Total costs'
GROUP BY left(fiscalyear_title,3)
) d
ORDER BY
CASE WHEN fiscalyear_title LIKE 'US%' THEN 0 WHEN fiscalyear_title <> 'Grand Total costs' THEN 1 ELSE 2 END ,left(fiscalyear_title,3),Sort





---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

anjali5
Posting Yak Master

121 Posts

Posted - 2014-05-09 : 10:34:46
quote:
Originally posted by tkizer

quote:
Originally posted by anjali5

In the above query, is it possible to put US at the top and then other countries come after that. Right now the query is ordered, but user always wants US at the top and then other countries.



ORDER BY CASE WHEN fiscalyear_title LIKE 'US%' THEN -1
WHEN fiscalyear_title = 'Grand Total Costs' THEN 1 ELSE 0 END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



It works, but all the totals were at the bottom so I changed this to

ORDER BY CASE WHEN fiscalyear_title LIKE 'US%' THEN -1
WHEN fiscalyear_title = 'Grand Total Costs' THEN 0 ELSE 1 END, LEFT(fiscalyear_title, 3), groupRank
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-09 : 12:30:55
Gotcha!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -