Analytical functions again - Dapi get your AF head on...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I think you already have the best solution here -- AF's wouldn't contribute anything to this situation.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width