select rightname,
count(distinct pid,pcucodeperson) as total,
sum(case when sex='1' then 1 else 0 end)male,
sum(case when sex='2' then 1 else 0 end)female
from person join cright on person.rightcode=cright.rightcode
where concat(person.pid,person.pcucodeperson)
not in(select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath
where deaddate <='2012-01-01'
or deaddate is not null)
AND person.rightcode IN (71,72,73,74,75,76,77,80,81,82,87,88,89)
group by person.rightcode
union select 'รวม' as rightname,
count(distinct pid,pcucodeperson) as total,
sum(case when sex='1' then 1 else 0 end)male,
sum(case when sex='2' then 1 else 0 end)female
from person join cright on person.rightcode=cright.rightcode
where concat(person.pid,person.pcucodeperson)
not in(select concat(persondeath.pid,persondeath.pcucodeperson)
from persondeath
where deaddate <='2012-01-01'
or deaddate is not null)
AND person.rightcode IN (71,72,73,74,75,76,77,80,81,82,87,88,89);
