|
|
|
Junior Member
 
Group: Forum Members
Last Login: 1/17/2007 5:30:00 AM
Posts: 14,
Visits: 1
|
|
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_custs
from
a
full outer join
b -- everywhere on ( a.date_of_day=b/c/d(...).date_of_day)
full outer join
d
full outer join
e
full outer join
f
full outer join
g
full outer join
h
left outer join
c
group by grouping sets ((c.month_id, a.date_of_day),(c.month_id),())
order by 1,2
And the result is:
200507. 2005-07-28 58317315
200507. 2005-07-29 54006999
200507. 2005-07-30 38717771
200507. 2005-07-31 22883162
200507. total 173925246
200508. 2005-08-01 43818067
200508. 2005-08-02 49287062
200508. 2005-08-03 48032243
200508. 2005-08-04 55144685
200508. 2005-08-05 63540968
200508. 2005-08-06 46978105
200508. 2005-08-07 26707536
200508. 2005-08-08 52050283
200508. 2005-08-09 42215595
200508. 2005-08-10 40481405
200508. 2005-08-11 94318002
200508. 2005-08-12 68535172
200508. 2005-08-13 48584137
200508. 2005-08-14 30915682
200508. 2005-08-15 49016040
200508. 2005-08-16 60961325
200508. 2005-08-17 56177854
200508. 2005-08-18 64274925
200508. 2005-08-19 60341100
200508. 2005-08-21 28196843
200508. 2005-08-22 47896784
200508. 2005-08-23 50897546
200508. 2005-08-24 42467145
200508. 2005-08-25 72119850
200508. 2005-08-26 61551026
200508. 2005-08-27 43815616
200508. 2005-08-28 25631179
200508. 2005-08-29 48931290
200508. 2005-08-30 51073884
200508. 2005-08-31 49427548
200508. total 1523388897
200508. total 2283454153 - it would be the 'total, total'
200509. 2005-09-01 50205687
200509. 2005-09-02 59044531
200509. 2005-09-03 44247926
200509. 2005-09-04 25701351
200509. 2005-09-05 44528623
200509. 2005-09-06 50506438
200509. 2005-09-07 50908409
200509. 2005-09-08 71730314
200509. 2005-09-09 64542775
200509. 2005-09-10 48846958
200509. 2005-09-11 31428499
200509. 2005-09-12 44448498
200509. total 586140009
I 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!
Best Regards, Gyozo Nagy
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/22/2008 12:30:19 PM
Posts: 115,
Visits: 16
|
|
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_group
order 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 101
Now 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_totals
from (
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 Totals
from devdata.feroz_test_group
group 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 1332
I 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 Totals
from devdata.feroz_test_group
group 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 606
this was not populating the NULL values I am not sure why??
anyways try this if this is acceptable for you.
Feroz Shaik
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 1/17/2007 5:30:00 AM
Posts: 14,
Visits: 1
|
|
Dear Feroz,
Unfortunately I will deal with this topic not before next week. But thanks for your ansver!
Best Regards, Gyozo Nagy
|
|
|
|