help with complex query

Hi,

is this what you are looking for?

/* solution build in sql server 2012 */
use [tempdb]
create table myData
(id int not null
,mcnt int not null
,status int not null
);
go

insert into mydata (id, mcnt, status) values(1, 10, 0);
insert into mydata (id, mcnt, status) values(1, 11, 1);
insert into mydata (id, mcnt, status) values(1, 10, 0);
insert into mydata (id, mcnt, status) values(1, 12, 0);
insert into mydata (id, mcnt, status) values(1, 12, 0);
insert into mydata (id, mcnt, status) values(1, 13, 1);
insert into mydata (id, mcnt, status) values(2, 10, 0);
insert into mydata (id, mcnt, status) values(2, 11, 0);
insert into mydata (id, mcnt, status) values(2, 11, 1);
go

with cteMcnt
as
(select id, count(mcnt) as "Count" from
(select distinct id, mcnt from myData) a
group by id),
cteSUM
as (select id, sum(status) as "Sum" from mydata
group by id)
select id, count, (select Sum from cteSUM where id = cteMcnt.id) as "Sum" from cteMcnt;

drop table mydata;

Regards,

Rudolf