-
Analytical functions again - Dapi get your AF head on...
Help needed again trying to improve a slow(ish) statement. I've learned from past discussions with you chaps, that AFs are rather quick.
Here's the brief. A catalogue mails customers. I need to know how many customers have been mailed once, how many mailed twice, etc.
We have a contact table with purn and catalogue_id row for each mailing. Purn is unique person id.
For example... Contact table contains
purn cat_id
123 55
123 55
123 55
456 55
456 55
678 55
678 55
789 55
I need output as follows
Cat_id 55 has mailed 1 person 3 times (i.e., purn 123)
Cat_id 55 has mailed 2 people 2 times (i.e., purns 456 & 678)
Cat_id 55 has mailed 1 person 1`time (i.e., purn 789)
Here's my real world example with output.....
select i.cid cat_id
,i.c no_times_mailed
,count(*) occurrences
from (select cat_id_for cid
,purn p
,count(*) c
from contact
where cat_id_for = 55
group by cat_id_for,purn) i
group by i.cid,i.c;
CAT_ID NO_TIMES_MAILED OCCURRENCES
--------- --------------- -----------
55 1 87190
55 3 32
55 2 16173
Can I do this with analytical functions ??
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
|