DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: count

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    232
    Hiiiiiiiiii,I have problem to get the count of the data from the table,
    my table is related to advtisements of users.
    ADVTISE_ID -- primarykey
    ADVTISE_TYPE ---- sales or wnated
    NEWENTRIES -- newentries
    addate -- advtisement posting date


    i need to display the data count from abc table based on above columns and date.
    Example--
    In my webpage counts like--
    WANTED 20
    SALES 30
    NEWENTRIES 5
    Here 20,30,5 is count
    But we like to display new entries weekly but sales and wanted entire rows count.
    I hope u can understand.
    plss help me
    thanks
    kavitha
    kavitha

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    Hi kavitha,
    It is not quite clear what you mean, but at a first glance it seems work for the decode function:
    select sum(decode(ADVTISE_TYPE , 'sales',1,0)) sales_count
    , sum(decode(ADVTISE_TYPE , 'wnated',1,0)) wnated_count
    , sum(decode(trunc(addate,'ww'),trunc(sysdate,'ww'),1,0)) newentries_count

    from TheTable;

    when you want all weeks of the new_entries
    it looks like the use of a union

    select ??refdate xx
    , sum(decode(ADVTISE_TYPE , 'sales',1,0)) sales_count
    , sum(decode(ADVTISE_TYPE , 'wnated',1,0)) wnated_count
    from TheTable
    group by ??refdate
    union
    select trunc(addate,'ww')
    , sum(newentries)
    from TheTable
    group by trunc(addate,'ww')
    Regards
    Ben de Boer

  3. #3
    Join Date
    Jun 2002
    Posts
    7

    Red face

    Hi,
    For newentries do you want to print 1 record for each week or only the last week count. I am not clear with your requirement. Why don't you try a query like this.

    Select ADVTISE_TYPE, count(*) as Count FROM abc where ADVISE_TYPE IN('WANTED', 'SALES')
    group by ADVTISE_TYPE
    UNION ALL
    Select ADVTISE_TYPE, count(*) as Count FROM abc where ADVISE_TYPE = 'NEWENTRIES' and adddate>(sysdate-7)
    group by ADVTISE_TYPE


    With regards
    V.Sathishkumar
    Your efforts may fail. But don't fail to make any efforts

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