Re: Is there a better way????

<hilljm1974_at_gmail.com> a écrit dans le message de news: 1141337763.143328.55000_at_z34g2000cwc.googlegroups.com...
| Is there a better way of doing the following query?
|
| SELECT TESTASMAKE MAKE, MAX(total_cnt) Total, MAX(pass_cnt) Pass,
| TO_CHAR(ROUND(((MAX(pass_cnt)/MAX(total_cnt))*100),3))||'%' Pass_Pct,
| MAX(rej_cnt) Rejected,
| TO_CHAR(ROUND(((MAX(rej_cnt)/MAX(total_cnt))*100),3))||'%' Rej_Pct,
| MAX(Crt_Cnt) Corrected,
| TO_CHAR(ROUND(((MAX(crt_cnt)/MAX(total_cnt))*100),3))||'%' Crtd_Pct,
| MAX(Abrt_Cnt) Aborted,
| TO_CHAR(ROUND(((MAX(abrt_cnt)/MAX(total_cnt))*100),3))||'%' Abort_Pct
| FROM (SELECT TESTASMAKE, COUNT(overallpf) Total_Cnt, 0 Pass_Cnt, 0
| Rej_Cnt, 0 Crt_Cnt, 0 Abrt_Cnt
| FROM new_testrecord
| GROUP BY TESTASMAKE
| UNION
| SELECT TESTASMAKE, 0 Total_Cnt, COUNT(overallpf) Pass_Cnt, 0 Rej_Cnt, 0
| Crt_Cnt, 0 Abrt_Cnt
| FROM new_testrecord
| WHERE overallpf='1'
| GROUP BY TESTASMAKE
| UNION
| SELECT TESTASMAKE, 0 Total_Cnt, 0 Pass_Cnt, COUNT(overallpf) Rej_Cnt, 0
| Crt_Cnt, 0 Abrt_Cnt
| FROM new_testrecord
| WHERE overallpf='2'
| GROUP BY TESTASMAKE
| UNION
| SELECT TESTASMAKE, 0 Total_Cnt, 0 Pass_Cnt, 0 Rej_Cnt, COUNT(overallpf)
| Crt_Cnt, 0 Abrt_Cnt
| FROM new_testrecord
| WHERE overallpf='3'
| GROUP BY TESTASMAKE
| UNION
| SELECT TESTASMAKE, 0 Total_Cnt, 0 Pass_Cnt, 0 Rej_Cnt, 0 Crt_Cnt,
| COUNT(overallpf) Abrt_Cnt
| FROM new_testrecord
| WHERE overallpf='6'
| GROUP BY TESTASMAKE)
| GROUP BY TESTASMAKE
| ORDER BY TESTASMAKE
|
| Thanks in advance!!!!
|

Yes. Have a look at DECODE.
For instance, "count(decode(overallpf,'6',1))" for abrt_cnt.

Regards
Michel Cadot Received on Thu Mar 02 2006 - 23:28:24 CST