需求：按照地区code分组求个数，并包括合计，先解释下rollup()函数，比group by 多一个总计

select

area_code,

   sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,

   sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum

from licence_manager.business_licence_phone_entity

where area_code is not null and area_code != ''

group by rollup(area_code)

1、第一种，采用union

select

'合计' as area_code,sum(legalPersonNum) as legalPersonNum,

sum(adminNum) as adminNum

from(

select

    area_code,

    sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,

    sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum

from licence_manager.business_licence_phone_entity

where area_code is not null and area_code != ''

group by area_code

order by area_code

)

union

select

   area_code,

   sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,

   sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum

from licence_manager.business_licence_phone_entity

where area_code is not null and area_code != ''

group by area_code

order by area_code

2、采用rollup()函数

select

   decode(grouping(area_code),1,'total',area_code) as area_code,

   sum(CASE WHEN owner_type = 0 THEN 1 ELSE 0 END) as legalPersonNum,

   sum(CASE WHEN owner_type = 1 THEN 1 ELSE 0 END) as adminNum

from licence_manager.business_licence_phone_entity

where area_code is not null and area_code != ''

group by rollup(area_code)

order by area_code