All Forums UDA
Apukad 5 posts Joined 03/05
14 Sep 2005
Grouping sets

Hello All,I have a problem, because i would like to order the query result with totals. But the ordered fields format are decimal(8,0)-month_id and date ('YYYY-MM-DD'). I tried to correct with coalesce, but it does not work in case of month_id.for example:Select coalesce(cast((c.month_id) as varchar(18)), 'total') month_id,coalesce(cast((a.date_of_day) as varchar(18)), 'total') date_of_day,sum(a.sales) (decimal (18,0)) turnover_of_store,sum(a.Inv) invoices_of_stores,sum(b.sales) (decimal (18,0)) turnover_of_petrol_station,sum(b.inv) invoices_of_petrol_station,sum(e.sales) (decimal (18,0)) store_and_ps_both,sum(e.inv) invoices_of_ps_and_st,sum(f.sales) (decimal (18,0)) only_in_petrol_station,sum(f.inv) invoices_only_ps,sum(g.sales) (decimal (18,0)) non_cust_turnover,sum(h.sales) (decimal (18,0)) noncust_petrol,sum(g.inv) invoices_of_non_custsfromafull outer joinb -- everywhere on ( a.date_of_day=b/c/d(...).date_of_day)full outer joindfull outer joine full outer joinf full outer joingfull outer joinhleft outer joincgroup by grouping sets ((c.month_id, a.date_of_day),(c.month_id),())order by 1,2And the result is:200507. 2005-07-28 58317315200507. 2005-07-29 54006999200507. 2005-07-30 38717771200507. 2005-07-31 22883162200507. total 173925246200508. 2005-08-01 43818067200508. 2005-08-02 49287062200508. 2005-08-03 48032243200508. 2005-08-04 55144685200508. 2005-08-05 63540968200508. 2005-08-06 46978105200508. 2005-08-07 26707536200508. 2005-08-08 52050283200508. 2005-08-09 42215595200508. 2005-08-10 40481405200508. 2005-08-11 94318002200508. 2005-08-12 68535172200508. 2005-08-13 48584137200508. 2005-08-14 30915682200508. 2005-08-15 49016040200508. 2005-08-16 60961325200508. 2005-08-17 56177854200508. 2005-08-18 64274925200508. 2005-08-19 60341100200508. 2005-08-21 28196843200508. 2005-08-22 47896784200508. 2005-08-23 50897546200508. 2005-08-24 42467145200508. 2005-08-25 72119850200508. 2005-08-26 61551026200508. 2005-08-27 43815616200508. 2005-08-28 25631179200508. 2005-08-29 48931290200508. 2005-08-30 51073884200508. 2005-08-31 49427548200508. total 1523388897200508. total 2283454153 - it would be the 'total, total'200509. 2005-09-01 50205687200509. 2005-09-02 59044531200509. 2005-09-03 44247926200509. 2005-09-04 25701351200509. 2005-09-05 44528623200509. 2005-09-06 50506438200509. 2005-09-07 50908409200509. 2005-09-08 71730314200509. 2005-09-09 64542775200509. 2005-09-10 48846958200509. 2005-09-11 31428499200509. 2005-09-12 44448498200509. total 586140009I do not understand why it is 200508. total (or 200507. total, because it seems to me random month_id). By the way values are correct, but I would like to see grand total at the bottom.Could you please inform me, if you have any idea in this topic? We have just updated our database to V2R6. Thanks in advance!

12 Jan 2006

Apukad, ( if you already go the results let me know if there is a good way)i am not sure if this will work for you or not. I tried this example and could get results by using derived tables.in my example i had the following rows. ( id did not use all the tables that u had i just made a dummy table with the following rows.select * from devdata.feroz_test_grouporder by 1 sale_date sale_amt 2005-01-11 30 2005-01-11 20 2005-01-11 10 2005-01-12 33 2005-01-12 22 2005-01-12 11 2006-01-01 300 2006-01-01 200 2006-01-01 100 2006-01-02 303 2006-01-02 202 2006-01-02 101Now i am trying to get the totals by day, totals by month and grand total.select coalesce(a.month_num, 'Grand_Total') MONTHS, case when a.month_num is NULL then 'Grand_Total' else (case when a.day_num is null then 'Monthly_Total' else a.day_num end) end DAYS, a.gr_totalsfrom (selectcoalesce(cast((sale_date/100) as varchar(18)),'Total') month_id, coalesce(cast(sale_date as varchar(18)), 'Total' ) day_of_month,sum(sale_amt) as Totalsfrom devdata.feroz_test_groupgroup by grouping sets ((month_id,day_of_month ),(month_id),()) ) a(month_num, day_num, gr_totals)order by 1 asc, 2 asc MONTHS DAYS gr_totals 10501 2005-01-11 60 10501 2005-01-12 66 10501 Monthly_Total 126 10601 2006-01-01 600 10601 2006-01-02 606 10601 Monthly_Total 1206 Grand_Total Grand_Total 1332I could not get the same result as you got by using the coalesce statements, when i did this with out using the derived table select coalesce(cast((sale_date/100) as varchar(18)),'Total') month_id, coalesce(cast(sale_date as varchar(18)), 'Total' ) day_of_month, sum(sale_amt) as Totalsfrom devdata.feroz_test_groupgroup by grouping sets ((month_id,day_of_month ),(month_id),())order by 1 asc, 2 asc month_id day_of_month Totals ? ? 1332 10501 ? 126 10501 2005-01-11 60 10501 2005-01-12 66 10601 ? 1206 10601 2006-01-01 600 10601 2006-01-02 606this was not populating the NULL values I am not sure why??anyways try this if this is acceptable for you.

Apukad 5 posts Joined 03/05
18 Jan 2006

Dear Feroz,Unfortunately I will deal with this topic not before next week. But thanks for your ansver!

You must sign in to leave a comment.