I am trying to calculate the percentage of the row events when the value is null. I have a table with 2 rows (zip_code, events).
TABLE weather
events TEXT
zip_code INTEGER
This is the query I wrote. I am just getting the count of all the events of every zip_code that are null:
SELECT zip_code, COUNT(*) AS percentage
FROM weather
WHERE events IS NULL
GROUP BY zip_code, events;
This is my output:
zip_code percentage
94041 639
94063 639
94107 574
94301 653
95113 638
I want to covert it into percentage, but I don't know how to get the total of events so I can do something like this COUNT(the events that are null)*100/COUNT(ALL).
Use the aggregate function AVG()
for the boolean expression events IS NULL
which evaluates to 0
for false
and 1
for true
:
SELECT zip_code,
AVG(events IS NULL) * 100 AS percentage
FROM weather
GROUP BY zip_code;
See a simplified demo.