select distinct count on different dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: select distinct count on different dates

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    select distinct count on different dates

    I want to select count of distinct requests for different statuses on different days like today, yesterday and day-before-yesterday from sample table, please guide me in this regard. Currently using following query where i get daily count.

    columns: req_Status, req_id,sent_date

    select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date,
    MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'1-NEW Command' Meaning_of_Status from sample
    where sent_date like sysdate
    and req_status=1 group by req_status
    union
    select req_status, count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date,
    MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'2-old Command' Meaning_of_Status from sample
    where sent_date like sysdate
    and req_status=2 group by req_status
    union
    .
    .
    and so on... column req_status has values in range from 1 to 12

    output:

    REQ_STATUS REQ_TODAY MIN_SENT_DATE MAX_SENT_DATE MEANING_OF_STAT
    ------------- ------------ --------------------- --------------------- -------------------
    1 3 25-JUL-12 - 06:47:20 25-JUL-12 - 07:28:24 1-NEW Command
    2 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:10 2-old Command
    3 3 25-JUL-12 - 06:47:20 25-JUL-12 - 08:07:29 3-wrong Command
    .
    .
    so on for 12 REQ_STATUSes

    In this output I need two more columns that will depict count of requests for different statuses for last two days as follows :

    REQ_STATUS | REQ_CNT_DB4YEST | REQ_CNT_YEST | REQ_TODAY | MIN_SENT_DATE | MAX_SENT_DATE | MEANING_OF_STAT
    Last edited by ora600; 07-25-2012 at 01:34 PM.

  2. #2
    Join Date
    Dec 2002
    Posts
    60
    You do not have anything in WHERE clause to indicate that you are selecting records for one day.

    You can use nested select statement for each day e.g.
    select req_status,

    (select count(*) from sample where req_status=1 and TRUNC(SENT_DATE)=TRUNC(sysdate)),

    count(distinct(req_id)) as REQ_TODAY, MIN(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Min_sent_date,
    MAX(to_char(SENT_DATE,'DD-MON-YY - HH:MI:SS')) as Max_sent_date,'1-NEW Command' Meaning_of_Status from sample
    where sent_date like sysdate
    and req_status=1 group by req_status

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