对表中的记录分组，并求每组所占百分比

[HDM1991]

有人做过对一个表中的记录分组，并求每组所占百分比的需求吗。

这个我的SQL语句，该SQL语句包含一个子查询，子查询对dns_messages表中满足过滤条件的记录通过hostname进行分组，计算每组的个数，并进行排序，然后主体部分把从子查询部分获取的结果（即每个分组的名字hostname和每个分组的个数nums）作为一个表tt，从中获取在分组的个数nums大于1000的记录中，从索引2开始起的10个记录

select * from (select hostname, count(*) as nums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" group by hostname order by nums desc, hostname desc) as tt where nums >= 1000 limit 2,10

现在的需求就是计算上述sql语句获取的10个记录中，每个分组的个数所占的百分比nums/sum。这个百分比的基数sum可以用如下的SQL语句表示，即上述SQL语句中的子查询部分获取的所有的分组的nums的和

select  sum(nums) from (select hostname, count(*) as nums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" group by hostname order by nums desc, hostname desc) as tt

我现在的疑问就是怎样在一条SQL语句中即获取分组，分组的个数，又获取百分比，难道要这样写

select hostname, nums, nums/sums, sums from (select hostname, count(*) as nums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" group by hostname order by nums desc, hostname desc) as tt, (select count(*) as sums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00")  as dd where nums >= 1000 limit 2,10

有其他方法吗?

[ACMAIN_CHM]

select hostname, count(*) as nums ,count(*) /sums,sums from dns_messages , (select count(*) as sums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" ) as dd where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" group by hostname order by nums desc, hostname desc

[HDM1991]

引用 1 楼 ACMAIN_CHM 的回复:

select hostname, count(*) as nums ,count(*) /sums,sums from dns_messages , (select count(*) as sums from dns_messages where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" ) as dd where capture_date >= "1991-01-01 00:00:00" and capture_date <= "2020-01-01 00:00:00" group by hostname order by nums desc, hostname desc

谢谢版主，看来如果想要在一条语句中完成，一个用于计算基数的子查询是少不了的了