테이블에 구분값이 01, 02, 03 이렇게 종류별로 있습니다..
일자가 20181108 에 01, 02 03, 합계값을 나오게 하려 합니다
쿼리문은
select sum(ti_qty), tick_kind from tbl_ticksale
where com_id = 'H'
and comp_id ='H01'
and sale_day = '20181108'
group by tick_kind
했는데 그날에 값은 01 6개 02, 3개 03 0 개였습니다
원하는 결과값은
01 6
02 3
03 0
이렇게 하고자 합니다
Comment 1
-
건우아빠
2018.11.28 18:13
------------------------------------------------------------- outer join-----------------------------------------------------------;with [temp_t] as(select '01' tick_kind union allselect '02' tick_kind union allselect '03' tick_kind )select b.tick_kind , isnull( sum(a.ti_qty),0 ) ti_qtyfrom tbl_ticksale a right join [temp_t] b on a.tick_kind = b.tick_kindand com_id = 'H'and a.comp_id ='H01'and a.sale_day = '20181108'group by b.tick_kind-------------------------------------------------------------outer apply-----------------------------------------------------------;with[temp_t] as(select '01' tick_kind union allselect '02' tick_kind union allselect '03' tick_kind )select *from [temp_t] aouter apply(select isnull( sum( ti_qty),0 ) ti_qtyfrom tbl_ticksalewhere com_id = 'H'and comp_id ='H01'and sale_day = '20181108'and tick_kind = a.tick_kind) b