sum distinct records in complicated decode query

From: john bruin <jbruin_at_tssc.co.nz>
Date: Tue, 24 Aug 1999 15:46:06 +1200
Message-ID: <37C2154A.1819@tssc.co.nz>

I have inherited the query below which works very well except in this case where one of the
columns (tar_status_status) occasionally contains a duplicate 'job end' code and this results in a inaccurate tally. Is there a work around for this?

I know I can get an accurate count by 'select count(distinct tar_status_status)' but I would really like to retain this format.

Any help appreciated.
John

select '5' "ORDER",
decode(t.tar_status_status,'02','Fixed02 ','') "Status",

sum(decode(a.fault_type,'S',1,0)) S,
sum(decode(a.fault_type,'F',1,0)) F,
sum(decode(a.fault_type,'H',1,0)) H,
sum(decode(a.fault_type,'U',1,0)) U,
sum(decode(a.fault_type,'DUP',1,0)) Dup,
sum(decode(a.fault_type,'N',1,0)) N,
sum(decode(a.fault_type,'DOC',1,0)) Doc
from tar a,
     tar_status t

WHERE a.tar = t.TAR_STATUS_TAR

and t.tar_status_mod_date between '1-JUL-99' AND '31-JUL-99'
and a.status_code = '02'
and t.TAR_STATUS_status = '02'
and a.assignee_mcode = 'abc'

group by t.tar_status_status
/  

0 status	S          F          H          U          DUP       
N          DOC       
- 		--------- ---------- ---------- ---------- ---------- ----------
---------- 
5 Fixed02       12          0          7         21         15        
21          0

Received on Mon Aug 23 1999 - 22:46:06 CDT