-
Grouping Problem
Hello,
I have the following query. I thouhgt that I could solve the grouping problem with a CASE statement. There is one Carrier that appears in a table under 3 different names (AWS, AWS Test and AWS Lab). I am trying to make it as one Carrier (AWS), but the query still returns 3 rows with the same Carrier: AWS. I would like to have the query returning only one row (AWS, AWS Test, AWS Lab) as AWS with merging all counts together.
Does anyone have a solution to my problem?
Thank You
The query:
SELECT /*+ index(C cdr_sum_dte) index(D device_info_carrier) */
CASE
WHEN d.carrier in ('AWS Lab','AWS Test') THEN 'AWS'
WHEN d.carrier in ('Rogers', 'Rogers/AT') THEN 'Rogers/AT&T'
WHEN d.carrier IS NULL OR d.carrier = 'Unknown' THEN 'Unknown Carrier'
ELSE d.carrier
END Carrier,
count(DISTINCT (CASE when c.srpid_type in (-1, 1,1004) and c.srpid_id not in (49777,49778,50853) then c.pin else 0 end)) BES,
count(DISTINCT (CASE when c.srpid_type in (5,6,7,9) then c.pin else 0 end)) BBEI,
count(DISTINCT (CASE when c.srpid_type in (12, 1005) or c.srpid_id in (49777,49778) then c.pin else 0 end)) BWC,
count(DISTINCT (CASE when c.srpid_type = 8 or c.srpid_id in (59392,2198,1517) then c.pin else 0 end)) ETP,
count(DISTINCT (CASE when c.srpid_type= 11 or c.srpid_id in (50912,50913,17250) then c.pin else 0 end)) P2P,
count(DISTINCT (CASE when c.srpid_type= 10 or c.srpid_id = 50853 then c.pin else 0 end)) TNPP,
count(DISTINCT (CASE when c.srpid_type= 13 or c.srpid_id = 62363 then c.pin else 0 end)) APB,
count(DISTINCT (CASE when c.srpid_type= 14 then c.pin else 0 end)) PMDS,
count(DISTINCT (CASE when c.srpid_type= 999 then c.pin else 0 end)) SNTP
FROM BBLG.cdr_summary@prldw C,
BBLG.device_info@prldw D
WHERE c.dte between to_date ('01-Dec-2003') and to_date('01-Dec-2003')
AND c.pin=d.pin(+)
GROUP BY Carrier
ORDER BY Carrier ASC
-
The only way I found was to copy the CASE statement to the GROUP BY clause (you can ORDER BY 1 to avoid having three copies of the CASE statement - GROUP BY 1, alas, does not work in 8i).
-
I am sorry, could you please show me what you mean. It is Friday and my brain is not functioning anymore. (I use Oracle 9i)
Thank you very much.
-
Nevermind, I still have a little bit of brain left. Thank you. It worked great.
Roman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|