group by grouping sets计算每个分组的占比

计算每个分组的数量

select parent_dict_code,count(*) from tb_data_dict group by rollup(parent_dict_code) ;

计算占比,注意要*1.0,否则仍为整型,全为0

select parent_dict_code,count(data_dict_id),(select count(data_dict_id) from tb_data_dict) as sum_all,count(data_dict_id)/(select count(data_dict_id) from tb_data_dict)*1.0
from tb_data_dict group by grouping sets(parent_dict_code) ;

 coalesce(parent_dict_code,’total’)本来是用来返回第一个非null值的,但是可以用来加一行合计数据:

select coalesce(parent_dict_code,’total’),count(data_dict_id) as g
from tb_data_dict group by grouping sets((parent_dict_code),())

    原文作者:木头大左
    原文地址: https://blog.csdn.net/luansj/article/details/119148400
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞