Hi I have a requirement of finding the occurrence percentage of specific items in a group. My table is like this
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 -------------------------------------
And the required result set is like this
Team Status Percentage ----------------------------------------- Team1 Submitted 71% Team1 Stretch 28% Team2 Submitted 50% Team2 Stretch 50% ------------------------------------------------
Im having just a group query like this
select Team,status,count(*) from Objectives group by Team Status
Im not getting an idea how to calculate the percentage from the group
Advertisement
Answer
You can use OVER Clause.
SELECT
Team,
Status,
COUNT(*) * 100 / SUM(COUNT(*)) OVER(PARTITION BY(Team)) AS Percentage
FROM Objectives
GROUP BY Team, Status
ORDER BY Team, Status Desc