SQL: Get all values in column without using “case when”

key |  num 
--------------
 A  |  2.6
 A  |  3.1
 B  |  2.5
 C  |  2.7

Let’s say I have a table as shown above, and I want the percent of key observations that take the value A. I can do this using the following case when statements:

sum(case when key = 'A' then 1.0 else 0 end)/count(*) as A_pct

However, if I have lots of values for key then I would have to write lots of case when statements. Is there a simpler way to do this?

Answer

Try this below script. This will return percentage for all distinct keys.

SELECT 
Key,
(COUNT(*)/(SELECT COUNT(*) FROM your_table)*1.00)*100.00 
FROM your_table
GROUP BY Key

If percentage is not required, then just do this as your sample code

SELECT 
Key,
COUNT(*)/(SELECT COUNT(*) FROM your_table)*1.00
FROM your_table
GROUP BY Key