| complex query question [message #38173] |
Wed, 27 March 2002 09:59  |
Lance Pris
Messages: 40
Registered: January 2002
|
Member |
|
|
I have a question that I am not sure if it possible. I want to select several date ranges:
Example
Select the number of solutions that are between last 30 days, 30-60 days, 60-90 days, 90-120 days…. etc
I am not sure how to do this in one query. Is it possible?
I was thinking doing it like this but it did not work..
Select (count(solution_id) as one from solution_table where pc_date between (sysdate-30) and sysdate),
(count(solution_id) as two from solution_table where pc_date between (sysdate-60) and (sysdate-30);
Can anyone give me some direction?
Thank you in advance
|
|
|
|
| Re: complex query question [message #38174 is a reply to message #38173] |
Wed, 27 March 2002 11:45  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
This should do it for you:
16:42:04 ==> drop table table1;
Table dropped.
16:42:16 ==> create table table1 (col1 number, col2 date);
Table created.
16:42:16 ==> insert into table1 values (1, sysdate-1);
1 row created.
16:42:16 ==> insert into table1 values (1, sysdate-5);
1 row created.
16:42:16 ==> insert into table1 values (1, sysdate-10);
1 row created.
16:42:16 ==> insert into table1 values (1, sysdate-31);
1 row created.
16:42:17 ==> insert into table1 values (1, sysdate-61);
1 row created.
16:42:17 ==> insert into table1 values (1, sysdate-62);
1 row created.
16:42:17 ==> insert into table1 values (1, sysdate-63);
1 row created.
16:42:17 ==> insert into table1 values (1, sysdate-64);
1 row created.
16:42:17 ==> select col1,
16:42:17 2 sum(case
16:42:17 3 when col2 between sysdate-30 and sysdate
16:42:17 4 then 1
16:42:17 5 else 0
16:42:17 6 end) count1,
16:42:17 7 sum(case
16:42:17 8 when col2 between sysdate-60 and sysdate-31
16:42:17 9 then 1
16:42:17 10 else 0
16:42:17 11 end) count2,
16:42:17 12 sum(case
16:42:17 13 when col2 between sysdate-90 and sysdate-61
16:42:17 14 then 1
16:42:17 15 else 0
16:42:17 16 end) count3
16:42:17 17 from table1
16:42:17 18 group by col1;
COL1 COUNT1 COUNT2 COUNT3
--------- --------- --------- ---------
1 3 1 4
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
