sql - SQL中组中存在的百分比

Question

嗨，我需要查找组中特定项目的出现百分比。我的桌子是这样的

Team     Objective     Status
--------------------------------
Team1      Obj1          Submitted
Team1      Obj2          Stretch
Team1      Obj3          Submitted
Team1      Obj4          Submitted
Team1      Obj5          Stretch
Team1      Obj6          Submitted
Team1      Obj7          Submitted
Team2      Obj1          Stretch
Team2      Obj2          Submitted
Team2      Obj3          Submitted
Team2      Obj4          Stretch
Team2      Obj5          Stretch
Team2      Obj6          Stretch
Team2      Obj7          Submitted
Team2      Obj8          Submitted
-------------------------------------

而所需的结果集是这样的

Team        Status        Percentage
-----------------------------------------
Team1     Submitted         71%
Team1     Stretch           28%
Team2     Submitted         50%
Team2     Stretch           50%
------------------------------------------------

我只有这样的组查询

select Team,status,count(*) from Objectives group by Team Status

我不知道如何计算该组的百分比

标签： sql

Answer 1

您可以使用OVER条款。

SELECT
    Team,
    Status,
    COUNT(*) * 100 / SUM(COUNT(*)) OVER(PARTITION BY(Team)) AS Percentage
FROM Objectives 
GROUP BY Team, Status
ORDER BY Team, Status Desc