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_total7 US.TEST 12.234 12.456 89.786 114.4767 US.TEST1 45.987 45.678 34.987 126.6527 US.TEST3 67.456 34.234 98.567 200.2577 GE.TEST1 678.987 34.456 34.567 748.0107 GE.TEST2 98.567 66.789 55.786 221.1427 NL.TEST1 67.567 34.567 98.789 200.9237 NL.TEST2 33.567 34.789 33.567 101.9237 NL.TEST3 99.678 44.678 34.567 178.9237* 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_total7 US.TEST 12.234 12.456 89.786 114.4767 US.TEST1 45.987 45.678 34.987 126.6527 US.TEST3 67.456 34.234 98.567 200.257 US. Total Costs 125.677 92.368 223.34 441.3857 GE.TEST1 678.987 34.456 34.567 748.0107 GE.TEST2 98.567 66.789 55.786 221.142 GE. Total Costs 777.554 101.245 90.353 969.1527 NL.TEST1 67.567 34.567 98.789 200.9237 NL.TEST2 33.567 34.789 33.567 101.9237 NL.TEST3 99.678 44.678 34.567 178.923 NL.Total Cost 200.812 114.034 167.923 481.7697* Grand Total Costs 1104.043 307.647 480.616 1892.306 I want total based on each title sofor 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 waySELECT FISCALYEAR_TITLE, SUM(FISCAL_DATA1), SUM(FISCAL_DATA2), SUM(FISCAL_DATA3), SUM(FISCAL_TOTAL)FROM myTableGROUP BY GROUPING SETS FISCALYEAR_TITLEbut 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_totalfrom ( 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) ) dorder by grp, srt, fiscalyear_titleOUTPUT:pk fiscalyear_titel fiscal_data1 fiscal_data2 fiscal_data3 fiscal_total----------- ----------------- ---------------------------------------------------------------------7 GE.TEST1 678.987 34.456 34.567 748.0107 GE.TEST2 98.567 66.789 55.786 221.1427 GE. total costs 777.554 101.245 90.353 969.1527 NL.TEST1 67.567 34.567 98.789 200.9237 NL.TEST2 33.567 34.789 33.567 101.9237 NL.TEST3 99.678 44.678 34.567 178.9237 NL. total costs 200.812 114.034 166.923 481.7697 US.TEST 12.234 12.456 89.786 114.4767 US.TEST1 45.987 45.678 34.987 126.6527 US.TEST3 67.456 34.234 98.567 200.2577 US. total costs 125.677 92.368 223.340 441.3857 Grand Total Costs 1104.043 307.647 480.616 1892.306 Be One with the OptimizerTG |
|
|
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 ALLSELECT 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 pkUNION ALLSELECT * FROM #tab WHERE fiscalyear_title like('GE%')UNION ALLSELECT 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 pkUNION ALL SELECT * FROM #tab WHERE fiscalyear_title like('NL%')UNION ALLSELECT 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 pkUNION ALLSELECT * FROM #tab WHERE fiscalyear_title like('Grand%') ---------------Murali KrishnaYou live only once ..If you do it right once is enough....... |
|
|
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 allselect 7 ,'US.TEST1' ,45.987 ,45.678 ,34.987 ,126.652 union allselect 7 ,'US.TEST3' ,67.456 ,34.234 ,98.567 ,200.257 union allselect 7 ,'GE.TEST1' ,678.987 ,34.456 ,34.567 ,748.010 union allselect 7 ,'GE.TEST2' ,98.567 ,66.789 ,55.786 ,221.142 union allselect 7 ,'NL.TEST1' ,67.567 ,34.567 ,98.789 ,200.923 union allselect 7 ,'NL.TEST2' ,33.567 ,34.789 ,33.567 ,101.923 union allselect 7 ,'NL.TEST3' ,99.678 ,44.678 ,34.567 ,178.923 union allselect 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 )srcGROUP 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 |
|
|
anjali5
Posting Yak Master
121 Posts |
Posted - 2014-04-24 : 14:28:46
|
Thank you all for your help. |
|
|
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. |
|
|
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. |
|
|
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 ENDTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
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_TOTALFROM ( 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) ) dORDER 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 KrishnaYou live only once ..If you do it right once is enough....... |
|
|
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 ENDTara KizerSQL Server MVP since 2007http://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 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|