select distinct cl.description 数据批次,
s.rolegroupdescription 拨打小组,
count(distinct oj.customer_guid)分配数量,
sum(case when t1.calltype=2 and t1.stringfield1='CTI' then 1 else 0 end) 拨打次数,
count(distinct case when t4.calltype=2 then t1.customer_guid end) 接通客户数,
sum(case when t4.calltype=2then 1 else 0 end) 接通个数,
sum(case when t1.calltype=2 and t1.stringfield1='CTI' and floor((t4.endtime- t4.starttime)*24*60)>1 then 1 else 0 end) 一分钟以上个数,
count(distinct co.appl_no) 成交件数,
sum(distinct year_premium) 成交业绩
from customer c
left join (select * from gd_ods_core where cntr_stat in ('K','L','M')) co on co.mobile=c.mobile
inner join objective oj on oj.customer_guid=c.customer_guid
left join event t1 on c.customer_guid=t1.customer_guid
inner join custlists cl on c.stringfield1= cl.custlist_xxx
inner join gd_temp_staff s on oj.tlallot =s.staff_id
inner join (Select customer_guid,max(createddate) as maxcreateddate from objective where tlallot is not null group by customer_guid)L
on oj.customer_guid=L.customer_guid and oj.createddate=L.maxcreateddate
left join record t4 on t4.customer_guid = t1.customer_guid and t4.event_guid = t1.event_guid and t4.staff_id = t1.handleby_id
where
cl.CustList_xxx='CustList_jtkh20170623hh32'
group by cl.description,s.rolegroupdescription
order by cl.description
现在问题是最后取成交金额sum(year_premium) 这里，因为数据重复，所以直接sum会大很多，但如果直接sum（distinct year_premium），会导致不同成交件，但相同交易金额的数給剔除，那就造成金额少了。
gd_ods_core成单表本身是唯一，但由于统计数据的要求，关联了电话拨打表“event”数据分配表“objective”和电话录音表“record”，客户电话拨打得越多，重复数量就越多，如果直接select 成单表gd_ods_core的话，要统计分配数量、拨打次数、接通次数就不准。
请帮忙提供如何根据appl_no(唯一单号)来sum year_premium(业绩金额)
或提供完全不同逻辑
两个图都没办法准确统计业绩金额。
