sql sum

hive -e "create table default.tbl_play_info_29 as
select p1,p2,deviceid,sum(case when pt <= 180 and pt >= 0 then 1 else 0 end) cor_num,sum(case when pt>180 or pt<0 then 1 else 0 end) err_num 
from data_raw.tbl_play_hour
 where dt='20150329' and act='time' group by p1,p2,deviceid;"

hive -e "create table default.tbl_pt_29_lzy as 
select a.p1,a.p2,a.app,sum(b.cor_num) cor_num,sum(b.err_num) err_num 
from default.tbl_app_info_29 a 
left outer join default.tbl_play_info_29 b 
on a.p1=b.p1 and a.p2=b.p2 and a.mac=b.deviceid 
group by a.p1,a.p2,a.app;"