Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Help with aggregate query

Re: Help with aggregate query

From: Kevin Crosbie <caoimhinocrosbai_at_yahoo.com>
Date: Wed, 13 Apr 2005 12:46:52 +0200
Message-ID: <1113389220.0b2574c0299a35fed06aa75ccb45c754@teranews>


You could try something like:
SUM(case when e.xstatus IN ('A','P','X') then 1 else 0 end) as regist instead of COUNT(e.xlocator)

and then remove the OR e.xstatus IN ('A','P','X') from the where clause.

"Randy Harris" <notvalid_at_no.spam> wrote in message news:pC37e.931$HK6.13_at_newssvr33.news.prodigy.com...
> I need help with this aggregate query.
>
> SELECT c.cancelled,c.xlocator,
> COUNT(e.xlocator) AS regist,
> c.xmaxcap,
> c.classtype,
> TO_CHAR(c.xstartdate, 'MM/DD/YYYY') AS startdate,
> c.xcode,c.version,
> c.site,c.xname,c.productfam,
> c.xsessions,c.xstarttime,
> c.xendtime,c.xseatchg
> FROM
> class c, event e
> WHERE
> c.xlocator=e.xlocator(+)
> AND TRUNC(xstartdate) > TRUNC(SYSDATE)
> AND cancelled IS NULL
> AND (e.xstatus IS NULL OR e.xstatus IN ('A','P','X'))
> GROUP BY
> c.xlocator,c.site,c.xcode,c.xname,c.xstartdate,c.xsessions,c.xseatchg,
> c.productfam,c.classtype,c.xmaxcap,c.version,
> c.xstarttime,c.xendtime,c.cancelled ;
>
>
> The part I haven't been able to figure out is:
>
> AND (e.xstatus IS NULL OR e.xstatus IN ('A','P','X'))
>
> This gets the counts correct, but if there are records where xstatus is
not
> null but the status is not in (A,P,X) the record isn't included, and I
need
> for it to be, with a count of 0. If I comment out that line, the queries
> get the correct records, but the counts are wrong. How can I control the
> counting seperate from the selection of records?
>
>
>
>
Received on Wed Apr 13 2005 - 05:46:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US