Re: SQL Aggregates

From: spencer <spencerp_at_swbell.net>
Date: Sat, 2 Sep 2000 19:55:53 -0500
Message-ID: <Vjhs5.377$a37.101812@nnrp2.sbc.net>

"Mike DiChiappari" <mike_at_mysticsoft.com> wrote in message news:8opfuu$jnp_at_dispatch.concentric.net...
> I am having trouble counting rows in a table. I have a table, T, with two
> columns: when and value. When is a timestamp and value is a varchar(10). I
> would like to create a report that shows values for a month. I can not
> create an SQL statement to show every day in the month and a corresponding
> count. The problem is that days without a particular value don't appear.
>
> Here is my query:
>
> SELECT TO_CHAR( when, 'MM/DD/YYYY'), COUNT(*)
> FROM T
> WHERE value = 'abc'
> GROUP BY TO_CHAR( when, 'MM/DD/YYYY')
>
> The results are fine except that days that don't have a single value with
> 'abc' are missing. I would like all days to show with a 0 value if 'abc' is
> missing.
>
> Any ideas about how I can do this?
>
> Thanks,
> Mike
>

it's very difficult to get a single SELECT statement to return data that does not exist in the table that you're querying. one common approach to solving this type of 'sparse data' problem is to create tables that contain all distinct values for the dimensions you need to return data for. here's what i would do:

create a table that contains all of the dates in the period i want to report on, and then query the 'detail' table, using an outer join to the 'dimension' table. the outer join syntax (+) allows me to return a row for each value in the dimension table, and the use of a decode expression allows me to return counts for more than one value.

here's a sample SQL*Plus script that comes close. fyi, the formatting of the statements is a lot clearer displayed in a fixed font. n.b. i've not tested this, so this may not be error free...

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY' /

• create a table to hold all dates for the period

CREATE TABLE my_period (when DATE NOT NULL, PRIMARY KEY (when)); /

• populate the 'period' table

DECLARE
  li PLS_INTEGER;
BEGIN
  FOR li IN 0..31 LOOP
    INSERT INTO my_period
    VALUES (to_date('08/01/2000','MM/DD/YYYY')+li);   END LOOP;
  COMMIT;
END;
/

SELECT p.when

     , SUM(DECODE(T.value,'abc',1,0)) AS "abc"
     , SUM(DECODE(T.value,'def',1,0)) AS "def"
  FROM T
     , my_period p

 WHERE p.when = TRUNC(T.when)(+)
   AND T.value IN ('abc','def')
 GROUP BY p.when
/

• if creating a table is not an option, and you want to include only
• dates that are in the table T, then the query could be re-written
• to replace the my_period table with a select statement, like this:

SELECT p.when

     , SUM(DECODE(T.value,'abc',1,0)) AS "abc"
     , SUM(DECODE(T.value,'def',1,0)) AS "def"
  FROM T
     , ( SELECT TRUNC(d.when)
           FROM T d
          GROUP BY TRUNC(d.when)
       ) p

 WHERE p.when = TRUNC(T.when)(+)
   AND T.value IN ('abc','def')
 GROUP BY p.when
/

• it may be possible to optimize (speed up) the query, perhaps by
• including a condition on T.when in the where clause... but that
• is really going to depend on the data, indexes, statistics, etc.
• but this should may be close to what you are looking for.

HTH Received on Sat Sep 02 2000 - 19:55:53 CDT