You Asked
Hi tom,
Here is My question
select branchcode,gscname,count(*) as totalno,'07 days'"days"
from test where (diffdays between 1 and 7)
group by branchcode,gscname
union
select branchcode,gscname,count(*) as totalno,'14 days'"days"
from test where (diffdays between 8 and 14)
group by branchcode,gscname
union
select branchcode,gscname,count(*) as totalno,'21 days'"days"
from test where (diffdays between 15 and 21)
group by branchcode,gscname
union
select branchcode,gscname,count(*) as totalno,'above 21 days'"days"
from test where diffdays > 21
group by branchcode,gscname
order by branchcode,"days"
I get answer as follows
branchcode gscname totalno days
101001 p 64 7
101001 p 21 14
101001 p 6 21
101002 j 10 7
101002 j 1 14
101002 j 1 21
- - - - -
i want another colomn which should give me the percentage as follows
1) for first row totalno 64 is how much percent of 64+21+6
2) for second row totalno 21 is how much percent of 64+21+6
3) for third row totalno 6 is how much percent of 64+21+6
4) for fourth row totalno 10 is how much percent of 10+1+1
5) for fifth row totalno 1 is how much percent of 10+1+1
5) for sixth row totalno 1 is how much percent of 10+1+1
and so on for next row
Just notice i want according to gscname column,first time gscname is
p,second time gscname is j.and third time it might be something else.
can You help me on This.
thanks,
Regards
ak
and Tom said...
Well, in 8.1.6 -- using the analytic functions, this will be a breeze and will perform much better then in 8.1.5 without them. I'll show you both methods.
I can say that using a union to do the above is rather inefficient (whenever you have mutually exclusive sets - use a UNION ALL. A union B = distinct ( sort ( A+B ) ). A union all B = (A+B).
But, we don't even need the union, just a little decode action:
ops$tkyte@DEV816> select branchcode, gscname, days, count(*) totalno
2 from (select branchcode, gscname,
3 decode( sign(diffdays-7.1), -1, '07 days',
4 decode( sign(diffdays-14.1), -1, '14 days',
5 decode( sign(diffdays-21.1), -1, '21 days',
6 'above 21'
7 )
8 )
9 ) days
10 from test
11 )
12 group by branchcode, gscname, days
13 order by 1, 3
14 /
BRANCHCODE G DAYS TOTALNO
---------- - -------- ----------
101001 p 07 days 64
101001 p 14 days 21
101001 p 21 days 6
101002 j 07 days 10
101002 j 14 days 1
101002 j 21 days 1
6 rows selected.
Now, to get the pct's in 8.1.5 and before, we'll use an inline view to aggregate the counts at two different levels and join them together:
ops$tkyte@DEV816> select branchcode, gscname, totalno, days,
round(100*totalno/tot_days,2) days_pct
2 from ( select branchcode, gscname, days, count(*) totalno
3 from (select branchcode, gscname,
4 decode( sign(diffdays-7.1), -1, '07 days',
5 decode( sign(diffdays-14.1), -1, '14 days',
6 decode( sign(diffdays-21.1), -1, '21 days',
7 'above 21'
8 )
9 )
10 ) days
11 from test
12 )
13 group by branchcode, gscname, days
14 ),
15 ( select branchcode t2_branchcode, gscname t2_gscname,
count(*) tot_days
16 from test
17 group by branchcode, gscname ) t2
18 where t2_branchcode = branchcode
19 and t2_gscname = gscname
20 order by 1, 4
21 /
BRANCHCODE G TOTALNO DAYS DAYS_PCT
---------- - ---------- -------- ----------
101001 p 64 07 days 70.33
101001 p 21 14 days 23.08
101001 p 6 21 days 6.59
101002 j 10 07 days 83.33
101002 j 1 14 days 8.33
101002 j 1 21 days 8.33
6 rows selected.
In Oracle8i release 2 and up (8.1.6) the analytic functions remove the need for us to materialize the aggregates at two different levels -- the ratio_to_report built in does what you want in one step. also notice how the use of CASE (new in 8.1.6 as well) makes the decoding easier to code/read/understand:
ops$tkyte@DEV816> select branchcode, gscname, totalno, days,
2 round( (ratio_to_report(totalno) over
3 ( partition by branchcode, gscname ))*100, 2 ) days_pct
4 from ( select branchcode, gscname, days, count(*) as totalno
5 from ( select branchcode, gscname,
6 case when diffdays between 1 and 7 then '07 days'
7 when diffdays between 8 and 14 then '14 days'
8 when diffdays between 15 and 21 then '21 days'
9 else 'Above 21 days'
10 end AS days
11 from t )
12 group by branchcode, gscname, days
13 )
14 order by 1, 4
15 /
BRANCHCODE G TOTALNO DAYS DAYS_PCT
---------- - ---------- ------------- ----------
101001 p 64 07 days 70.33
101001 p 21 14 days 23.08
101001 p 6 21 days 6.59
101002 j 10 07 days 83.33
101002 j 1 14 days 8.33
101002 j 1 21 days 8.33
6 rows selected.
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment