Re: count(*) in sql statement

From: Michael Ryan <ryan_at_xsoft.xerox.com>
Date: Tue, 31 May 1994 18:24:56 GMT
Message-ID: <1994May31.182456.13822_at_xsoft.xerox.com>

In article np7_at_hsc.usc.edu, mcitron_at_hsc.usc.edu (Mark Citron) writes:
>Is it possible to select two different counts in one statement? That is
>I have a list of dates, can I select the number of dates between dateA and dateB
>and also the number of dates between dateC and dateD? All on one line of output.

the best answer to your question is, perhaps, another question: can you select, in one statement, all the rows with dates between dateA and dateB plus all the rows with dates between dateC and dateD and diffentiate them distinctly?

i believe you can, if you split the data set, duplicate, then differentiate.

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

to make life simple on myself, i'm going to use a table i have lying around. note only the date column's really important:

create table abc ( i integer, c char(20), d date, f real );

note that the date column includes one NULL entry. this means we can split our data set around 15-may-92, an arbitrary date, into 3 sets: NULL (1), < (2), > (3).

I          C                    D          F         
---------- -------------------- ---------- ----------
1          12345678901234567890 11/3/93    99.99     
2          12345678901234567890 12/6/93    0.00012   
42         whats' the question? 10/21/83   42        
55         the limit                                 
20000      simply put           10/23/53   9876.12345
451        farenheit            5/15/94    451.54133 

let's isolate the data sets in which we are interested:

select count ( a.d ) from abc a
where a.d between '15-jan-47' and '15-may-92' COUNT(A.D)

---

2
1 row retrieved.

select count (b.d) from abc b
where b.d between '16-may-92' and '21-may-94' COUNT(B.D)

---

3
1 row retrieved.

attempting to combine the 2 sets yields what appears to be a useless answer:

select count ( a.d ) , count (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' COUNT(A.D) COUNT(B.D)
---------- ----------
6 6
1 row retrieved.

...NOW HERE'S THE IDEA... note how the columns have the correct data just duplicated:

select a.d , 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' D D
---------- ----------

10/21/83   11/3/93   
10/23/53   11/3/93   
10/21/83   12/6/93   
10/23/53   12/6/93   
10/21/83   5/15/94   
10/23/53   5/15/94   

6 rows retrieved.

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' COUNT(DISTINCTA.D) COUNT(DISTINCTB.D)

------------------ ------------------
2                  3                 

1 row retrieved.

QED. strong disclaimers apply for efficienc - this is an MxN answer.

anyone have a better answer?

good luck
//michael

---
::
:: michael ryan
:: ryan_at_xsoft.xerox.com
:: 415 813 7620
:: ob disclaimer: opinions expressed are not those of xerox, corp.

Received on Tue May 31 1994 - 20:24:56 CEST