sql - 求总后的平均值

Question

我有疑问

SELECT language, COUNT(language) AS countx, sum(COUNT(language)) OVER() AS totalcount
FROM table1
where Language is not null
GROUP BY Language

结果

language    countx  totalcount
English     1672    1693
Spanish       21    1693

不知道如何从每组的 countx/totalcount 获得 AVG，所以 1672/1693 和 21/1693。
谢谢你。

标签： sqlsql-server

Answer 1

只需划分：

SELECT language, COUNT(language) AS countx,
       sum(COUNT(language)) OVER () AS totalcount,
       COUNT(language) * 1.0 / sum(COUNT(language)) OVER()
FROM table1
WHERE Language is not null
GROUP BY Language ;

一些数据库进行整数除法，这解释了* 1.0.