select distinct count on different dates

[ora600]

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

[skhanal]

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