1

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:

Id / Scenario 1 2 3 4
A 2 4 1 3
B 2 7 5 6

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

Desired output:

Id / Scenario 1 2 3 4
A 20% 40% 10% 30%
B 10% 45% 25% 30%

How can I do this? Thank you.

Jean Picard
  • 51
  • 6

1 Answers1

0

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

William Lafond
  • 43
  • 4