How to have SQL return a percentage value?

Question

If I'm asked a question asking finding the percentage of people that selected red on lets say color_picked from table1

Is there a more efficient way instead of manually doing the math?

Table1

color_picked    
Red - 10    
Blue - 20    
green- 70

Answer 1

Silly example data, but the query at the end should do what you want. It will return a number in (0,1]. If you want it to show in (0,100], then change it to multiply by 100.0 instead of 1.0.

CREATE TABLE Table1 (person_id int, color_picked text);

INSERT INTO Table1 (person_id, color_picked)
VALUES
(1,'Red'),
(2,'Blue'),
(3,'Blue'),
(4,'Green'),
(5,'Green'),
(6,'Green'),
(7,'Green'),
(8,'Green'),
(9,'Green'),
(10,'Green');

SELECT color_picked, COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS pct_color
FROM Table1
GROUP BY color_picked;