sql - SQL 平均百分比（按组）

Question

我有一些示例记录的 table1（将有超过 100 条记录）

lastname    courtesy    
Lane          3  
Lane          4  
Lane          5  
Lane         12   
Santana       4  
Santana       5  

我的查询是按姓氏分组并没有按姓氏分组，
您可以看到 Lane 和 Santana 仍然没有分组。
请更正查询。谢谢你。

SELECT
  lastname,
  courtesy,
  percentage = AVG(courtesy) * 100 / SUM(AVG(courtesy)) OVER (PARTITION BY lastname)
FROM echo
GROUP BY
  lastname, courtesy

结果

lastname    courtesy    percentage
Lane         3           12
Lane         4           16
Lane         5           20
Lane         12          50
Santana      4           44
Santana      5           55

结果看起来像这样

lastname    percentage
Lane         16        (4/24)
Satana       22        (2/9)

count (lastname) / sum (courtesy)  group by lastname

标签： sqlsql-server

Answer 1

我认为这是您想要的逻辑：

SELECT lastname,
       SUM(SUM(courtesy)) OVER (ORDER BY lastname),
       COUNT(*) * 1.0 / SUM(courtesy)
FROM echo
GROUP BY lastname;