"Randy Harris" <notvalid_at_no.spam> wrote in message
news:Wef7e.2070$716.673_at_newssvr19.news.prodigy.com...
> "Kevin Crosbie" <caoimhinocrosbai_at_yahoo.com> wrote in message
> news:1113389220.0b2574c0299a35fed06aa75ccb45c754_at_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?
> > >
> > >
>
> I tried creating my own function
>
> CREATE OR REPLACE FUNCTION StatusCount(xstat VARCHAR)
> RETURN NUMBER
> IS
> BEGIN
> IF xstat = 'A'
> OR xstat = 'P'
> OR xstat = 'X' THEN
> RETURN 1;
> ELSE
> RETURN 0 ;
> END IF;
> END ;
>
> I then changed this line:
> COUNT(e.xlocator) AS regist,
> to:
> SUM(statuscount(e.xlocator)) AS regist,
>
> But the counts are wrong. They all come out either 0 or 1, nut "summed".
> Can someone please tell me what I am doing wrong?
>
Found the problem -
should have been
SUM(statuscount(e.xstatus)) AS regist,
Received on Wed Apr 13 2005 - 17:14:21 CDT
