In SQL I am trying to do a table with percentages for each row only. I've looked everywhere but can't quite get it. count(*)/sum(count(*)) over () is close but that does % of everything.

Data:
ID = ['A','B',...]
Scenario = [1,2,3,4]

SQL:

SELECT * FROM(
    SELECT
      ID,
      Scenario,
      count(*) as count,
FROM `table`
GROUP BY `ID`,
         `Scenario`
)
pivot(
  sum(count) as total
  for Scenario in (1,2,3,4)
)

Current output:

This is good, but I also want to display this as % of each row so each row should add up to 100%.

Desired output:

How can I do this? Thank you.

CodePudding user response：

So basically what you would try to do for A-1 is A-1/A? Would Scenario/Total * 100 give you the correct value? then just concat a % sign

CodePudding user response：

Does this give you the intended results?

SELECT
  ID,
  Scenario,
  COUNT(*)*100/(SELECT COUNT(*) FROM `table` WHERE Scenario IN (1,2,3,4)) AS percentage
FROM 
  table
GROUP BY 
  ID, Scenario
WHERE 
  Scenario IN (1,2,3,4);