最近在实习的时候遇到了这个问题,但是看了网上的答案基本都是mysql数据库的,这里我花了半天的时间总结一个通用的方法,现在分享给大家
方法一:
SELECT 列名 ,COUNT(*) total,
CONCAT(round( CAST(count(列名) AS numeric) / CAST((SELECT COUNT(*) FROM "表名") AS numeric),2)*100 ,'%') AS percentage
FROM
"表名"
GROUP BY
列名
ORDER BY
列名
方法二:(简洁版)
SELECT 列名 ,COUNT(*) total,
CONCAT(round(count(列名)::numeric /(SELECT COUNT(*) FROM "表名"),2)*100 ,'%') AS percentage
FROM
"表名"
GROUP BY
列名
ORDER BY
列名
方法三:(补充)
SELECT 列名, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM "表名"
GROUP BY 列名;
下面我举个列子
表结构如下:
SELECT age ,COUNT(*) total,
CONCAT(round( CAST(count(age) AS numeric) / CAST((SELECT COUNT(*) FROM "Classroom") AS numeric),2)*100 ,'%') AS percentage
FROM
"Classroom"
GROUP BY
age
ORDER BY
age
SELECT 列名 ,COUNT(*) total,
CONCAT(round(count(列名)::numeric /(SELECT COUNT(*) FROM "表名"),2)*100 ,'%') AS percentage
FROM
"表名"
GROUP BY
列名
ORDER BY
列名
查询结果如下: