Posted by Ed Murphy on 02/27/07 17:59

Sam wrote:

> MyTable {field1, field2, startdate, enddate}
>
> I want to have the count of field1 between startdate and enddate, and
> the count of field2 where field2 = 1 between startdate and enddate,
> all in the same query.

Based on your sample query, it appears that you want

1) all rows where (startdate to enddate) falls within given limits
2) separate subtotals for each distinct value of field1 where 1) is true

(This is why Erland, Celko, et al. keep ranting about "post your
CREATE TABLE, sample data, and desired output" - because it largely
eliminates the need to make educated guesses such as the above.)

Anyway, this should cover it:

select field1, count(field1), sum(case field2 when 1 then 1 else 0)
from MyTable
where startdate >= '01-24-2007' and enddate <= '02-25-2007'
group by field1

[Back to original message]