Re: count(*) in sql statement

From: Philip C. Blain <blain_at_shell.com>
Date: Sat, 4 Jun 1994 18:10:02 GMT
Message-ID: <BLAIN.94Jun4121002_at_tikal.shell.com>

>>>>>

you're probably aware, because of your explorations, of an example like the following. this example illustrates the paradox and the solution:

[clip]

note how the columns have the correct data just duplicated:

[clip]

select count(distinct a.d), count(distinct b.d) from abc a, abc b
where a.d between '15-jan-47' and '15-may-92' and b.d between '16-may-92' and '21-may-94' <<<<<

Ah. The harmony of relational databases and mathematical theory. I will not claim that my solution is any better. In some cases it could go either way. Note also that your solution ignores the case of multiple rows with the same date (if the date is truncated).

select
  count(distinct
    decode( sign( trunc(to_date('31-Aug-93')-tip_date+1) ), 1,

      decode( sign( trunc(tip_date-to_date('01-Aug-93')+1) ), 1,
        trunc(tip_date)

  ) ) ) "A1",
  count(distinct
    decode( sign( trunc(to_date('01-Jul-94')-tip_date+1) ), 1,

      decode( sign( trunc(tip_date-to_date('01-Jan-94')+1) ), 1,
        trunc(tip_date)

  ) ) ) "B1",
  count(
    decode( sign( trunc(to_date('31-Aug-93')-tip_date+1) ), 1,

      decode( sign( trunc(tip_date-to_date('01-Aug-93')+1) ), 1,
        tip_date

  ) ) ) "A2",
  count(
    decode( sign( trunc(to_date('01-Jul-94')-tip_date+1) ), 1,

      decode( sign( trunc(tip_date-to_date('01-Jan-94')+1) ), 1,
        tip_date

  ) ) ) "B2"
from
  sam_tipsu
;

Note that count will not include any nulls. Also, a where clause can be added to facilitate the use of an index rather than a full table scan.
The basic idea is to only work with what we need. Doing the join will result in "scratch" work. This just feeds me. In some cases this method is really nice (such as a bunch of tables, some may or may not have entries, so just outer join the lot (giving the MxN), then just decode them, and get a distinct count). (Hmm, when's the next IOSCC (International Obfuscated Sql Code Contest 8-) ).

Here's what we get:

---

        A1 B1 A2 B2 ---------- ---------- ---------- ----------

         4 7 26 7

Here's the initial picture:

---

select trunc(tip_date), count(*)
from sam_tipsu
group by trunc(tip_date);

TRUNC(TIP COUNT(*)

--------- ----------
19-AUG-93         22
20-AUG-93          1
27-AUG-93          1
30-AUG-93          2
10-SEP-93          1
13-SEP-93          2
23-SEP-93          1
22-OCT-93          1
25-OCT-93          2
02-NOV-93          1
22-DEC-93          1
04-JAN-94          1
17-FEB-94          1
21-FEB-94          1
07-MAR-94          1
25-MAR-94          1
12-MAY-94          1
25-MAY-94          1

18 rows selected.

Thanks, -phil- Received on Sat Jun 04 1994 - 20:10:02 CEST