mysqlsqlgroup-byaveragesql-null

Get the percentage of row with TEXT type

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).

Solution

  • 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.