SQL calculate percent from categorical column-CodePudding

Table

Name	Color
Phil	Red
Phil	Red
Phil	Red
Phil	Blue
Joe	Blue
Joe	Red

desired output

Name	Color	Pct
Phil	Blue	25
Phil	Red	75
Joe	Red	50
Joe	Blue	50

this is what I thought would work but does not for me

select Name, Color, count(color) / sum(color) as pct
from
group by Name, color

CodePudding user response：

You need to divide the count of the current value for the combination of color and nam (I always avoid potential reserved words for object names) by the count of the current value for nam. This can be solved by OLAP (window) functions with different OVER() clauses.

As OLAP functions return the same number of rows as the input, we need implicit grouping on all columns, hence a DISTINCT clause.

WITH
indata(Nam,Color) AS (
          SELECT 'Phil','Red'
UNION ALL SELECT 'Phil','Red'
UNION ALL SELECT 'Phil','Red'
UNION ALL SELECT 'Phil','Blue'
UNION ALL SELECT 'Joe','Blue'
UNION ALL SELECT 'Joe','Red'
)
SELECT DISTINCT
  nam
, color
, COUNT(*) OVER(PARTITION BY nam) AS nam_occ_count
, COUNT(*) OVER(PARTITION BY nam,color) AS color_count
,   100 
  * COUNT(*) OVER(PARTITION BY nam,color) 
  / COUNT(*) OVER(PARTITION BY nam) 
  AS pct
FROM indata
ORDER BY 3;
 nam  | color | nam_occ_count | color_count | pct 
------ ------- --------------- ------------- -----
 Joe  | Blue  |             2 |           1 |  50
 Joe  | Red   |             2 |           1 |  50
 Phil | Blue  |             4 |           1 |  25
 Phil | Red   |             4 |           3 |  75