Analytical functions again - Dapi get your AF head on...

[Horace]

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 ??

[slimdave]

I think you already have the best solution here -- AF's wouldn't contribute anything to this situation.