-
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 12:34 PM.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|