Postgre数据库：统计某一属性在字段中的百分比

最近在实习的时候遇到了这个问题，但是看了网上的答案基本都是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
列名

查询结果如下：