데이터를 확인하는 것이 힘이 드는걸요.

create table구문과

insert 구문을 보여준 샘플데이터에 의거한 모양으로 주고

결과의 모양을 내어주는게 같이 고민하는데 도움이 될 듯한데요 ^^

앗 이거 엉망이군요.^^;; 일단 보기편하게 수정하였습니다.

create table #work (
  uid varchar(10)
, f_work varchar(10)
, s_work varchar(10)
, t_work varchar(10)
, page int
)
insert into #work values ('aa', 'aa', null, 'aa', 3)
insert into #work values ('aa', 'aa', 'aa', null, 2)
insert into #work values ('bb', 'bb', 'bb', null, 2)
insert into #work values ('bb', null, 'bb', null, 1)
insert into #work values ('cc', 'cc', 'cc', null, 1)
insert into #work values ('cc', 'cc', null, 'cc', 1)
insert into #work values ('dd', null, 'dd', 'dd', 3)

select * from #work
go

select uid
        , count(f_work) as f_count, case when sum(f_page) is null then 0 else sum(f_page) end as f_page_sum
        , count(s_work) as s_count, case when sum(s_page) is null then 0 else sum(s_page) end as s_page_sum
        , count(t_work) as t_count, case when sum(t_page) is null then 0 else sum(t_page) end as t_page_sum
  from (select uid, f_work, s_work, t_work
                   , case when f_work is not null then page end as f_page
                   , case when s_work is not null then page end as s_page
                   , case when t_work is not null then page end as t_page
      from #work) tmp
  group by uid

역시 table은 만들 때 잘 만들어야겠죠.. ^^;;

그럼..

감사합니다.많은 도움이 됬네요 ^^

select COALESCE(f_work, s_work, t_work) workid
         ,Count(f_work)                                f_count
        ,IsNull(Sum(case when f_work is not null then page end), 0) f_page_sum
        ,Count(s_work)                                s_count
        ,IsNull(Sum(case when s_work is not null then page end), 0) s_page_sum
        ,Count(t_work)                                 t_count
        ,IsNull(Sum(case when t_work is not null then page end), 0) t_page_sum
from work
group by COALESCE(f_work, s_work, t_work)

머 같은 방식이지만...

이렇게도 되겠네요...