Re: Need query to group by date

Bob,

Try this:

SELECT a.orderstatus,

        a.less_then_30,
	b.in_30_60,
	c.in_60_90,
	c.over_90
FROM    (SELECT    orderstatus,
                   count(*) less_then_30
        FROM       orders
        WHERE      orderdate > sysdate - 30
        GOURP BY   orderstatus) a,
        (SELECT    orderstatus,
                   count(*) in_30_60
        FROM       orders
        WHERE      orderdate <= sysdate - 30
        AND        orderdate > sysdate - 60
        GOURP BY   orderstatus) b,
        (SELECT    orderstatus,
                   count(*) in_60_90
        FROM       orders
        WHERE      orderdate <= sysdate - 60
        AND        orderdate > sysdate - 90
        GOURP BY   orderstatus) c,
        (SELECT    orderstatus,
                   count(*) over_90
        FROM       orders
        WHERE      orderdate < sysdate - 90
        GOURP BY   orderstatus) d,
WHERE   a.orderstatus = b.orderstatus
AND     b.orderstatus = c.orderstatus
AND     c.orderstatus = d.orderstatus;

-- 
Regards,

Zbigniew Sliwa
Oracle Programmer
Poland
email: zibi_at_at_yahoo.com

Bob McConnell wrote:


> 

> Thanks, but that doesn't give me what I'm looking for.

> I need the results to look like this:

> ORDERSTATUS    30    30-60    60-90   >90

> Canceled                3        5          2        7

> Complete               50     125         76     345

> INPROCESS          45       23         98     567

> etc.

> 

> Bob

> 

> OasisFan wrote:

> 

> > Bob McConnell napisa³(a) w wiadomoœci: <39BFE46F.D6FAEBE1_at_mci.com>...

> > >I have a table of ORDERS which has an OrderDate field and an OrderStatus

> > >field.  I would like to query to get orders <30 days, 30 to 60days, 60

> > >to 90days and > 90  grouped by Orderstatus.  How would I do that?

> > >

> > >thanks

> > >Bob

> > >

> >

> > Try UNION operator...

> >

> > Select  orderstatus, count(*)

> >     from XXX

> >   where orderdate < 30 days

> >    group by orderstatus

> > UNION

> > Select  orderstatus, count(*)

> >     from XXX

> >   where 30 days < orderdate < 60 days

> >    group by orderstatus

> > UNION

> > Select  orderstatus, count(*)

> >     from XXX

> >   where orderdate > 60 days

> >    group by orderstatus;

> >

> > (sorry for syntax..its not quite correct).

> > buy it may help.