GROUP BY and add calculate percentage-CodePudding

I have following table:

%alcohol	rating	count
7	4	5
6	5	3
7	6	3
4	7	2
4	8	1
4	9	5

with SQL commands I want to calculate the partition of the %alcohol:

%alcohol	rating	count	percentage
4	7	2	25
4	8	1	12.5
4	9	5	62.5
6	5	3	100
7	4	5	62.5
7	6	3	37.5

I already tried it with following command, but it didnt work

WITH number_of_rating AS
(
  SELECT AlcoholPercentage, Rating, Count(*) AS number_of_rating_per_percentage
  FROM Beer
  GROUP BY Beer, Rating)
SELECT AlcoholPercentage, Rating, number_of_rating_per_percentage, 
  (0.0 number_of_rating_per_percentage)/(COUNT(*) OVER (PARTITION BY AlcoholPercentage)) AS Portion
FROM number_of_rating;

CodePudding user response：

Most databases support Window functions:

select *, 
  "Count" * 1.0 / Sum("count") over(partition by "%alcohol") * 100 as Percentage
from Beer

CodePudding user response：

WITH CTE(ALCOHOL,RATING,COUNT)AS
(
  SELECT  7 ,   4 , 5 UNION ALL
  SELECT 6 ,    5 , 3 UNION ALL
  SELECT 7 ,    6,  3 UNION ALL
  SELECT 4 ,    7,  2 UNION ALL
  SELECT 4 ,    8,  1 UNION ALL
  SELECT 4 ,    9,  5
)
 SELECT C.ALCOHOL,C.RATING,C.COUNT,
 CAST((CAST(C.COUNT AS DECIMAL(20,2))/CAST(SUM(C.COUNT)OVER(PARTITION BY C.ALCOHOL)AS DECIMAL(20,2)))*100.00 AS DECIMAL(20,2)) AS PER_CENTAGE
FROM CTE AS C

Enjoy your beer