Posted by AlterEgo on 02/27/07 17:56

Sam,

Is this what you're looking for?

create table MyTable (Field1 int, Field2 int)
insert MyTable select 1,1
insert MyTable select 1,1
insert MyTable select 2,1
insert MyTable select 2,2

select
Field1
, count(*) as Field1Count
, sum(
case when Field1 = Field2 then 1
else 0
end
) as Field2Count
from MyTable
group by Field1

-- Bill

"Sam" <samuelberthelot@googlemail.com> wrote in message
news:1172595874.848619.21890@v33g2000cwv.googlegroups.com...
> Hi,
>
> I have one table like :
>
> 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.
>
> I know how to get the first part (see below), but i don't know how to
> include the count of field2 where field2 = 1 in each row.
>
> select count(field1) as field1
> from MyTable
> where startdate >= '01-24-2007' and enddate <= '02-25-2007' and field2
> = 0
> group by field1
>
>
> Can you help ?
> thanks
>

[Back to original message]