Help with aggregate query

From: Randy Harris <notvalid_at_no.spam>
Date: Wed, 13 Apr 2005 07:11:17 GMT
Message-ID: <pC37e.931$HK6.13@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 - 02:11:17 CDT