Re: Help with aggregate query

From: Randy Harris <notvalid_at_no.spam>
Date: Wed, 13 Apr 2005 20:25:26 GMT
Message-ID: <Wef7e.2070$716.673@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? Received on Wed Apr 13 2005 - 15:25:26 CDT