Re: sql query

GM wrote:
>
> I have a table with 800Millian rows. I want to write a sql query where i
> want to find out how many rows fall between certain dates. It is bascially
> find out each quater data in year for last 6 years.
>
> Basically combining the below statments into one single sql query
>
> select count(*) from orddet where order_date >= '01-JAN-1996' and order_date
> <= '31-MAR-1996';
> select count(*) from orddet where order_date >= '01-APR-1996' and order_date
> <= '30-JUN-1996';
> select count(*) from orddet where order_date >= '01-JUL-1996' and order_date
> <= '30-SEP-1996';
> select count(*) from orddet where order_date >= '01-OCT-1996' and order_date
> <= '31-DEC-1996';
>
> select count(*) from orddet where order_date >= '01-JAN-1997' and order_date
> <= '31-MAR-1997';
> select count(*) from orddet where order_date >= '01-APR-1997' and order_date
> <= '30-JUN-1997';
> select count(*) from orddet where order_date >= '01-JUL-1997' and order_date
> <= '30-SEP-1997';
> select count(*) from orddet where order_date >= '01-OCT-1997' and order_date
> <= '31-DEC-1997';

I see two possible solutions:

1.
select count(*), 'First quarter 1996' from orddet where order_date >= '01-JAN-1996' and order_date <= '31-MAR-1996' UNION
select count(*), 'Second quarter 1996' from orddet where order_date >= '01-APR-1996' and order_date <= '30-JUN-1996';

2.
select count(a.*) "First quarter 1996", count(b.*) "Second quarter 1996" from orddet a, orddet b
where (a.order_date >= '01-JAN-1996' and a.order_date <= '31-MAR-1996') and (b.order_date >= '01-APR-1996' and b.order_date <= '30-JUN-1996');

BTW, take a look at your date format, use trunc(order_date). Regards,

Knut Received on Tue Jun 18 2002 - 13:59:00 CDT