Get the percentage of row with TEXT type-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

Two things.

You need to know the total number of rows to get a percentage.
When doing this, don't group by event.

This should do it.

SELECT zip_code, 
       100.0 * (SUM(events IS NULL)) / COUNT(*)) AS percentage
FROM weather
GROUP BY zip_code;

SUM(events IS NULL) is MySQL-specific syntax, because MySQL Boolean expressions such as events IS NULL evaluate to either 1 or 0. To be portable you'd write it SUM(CASE WHEN events IS NULL THEN 1 ELSE 0 END).

By the way, if you can use a VARCHAR() type in place of TEXT you'll get better performance.

CodePudding user response：

CREATE table #tblNulls 
(
    zip_code INTEGER,
    Nullcount integer
)

CREATE TABLE #tblAll
(
    zip_code INTEGER,
    Allcount integer
)

INSERT INTO #tblNulls
SELECT zip_code, COUNT(*) AS Nullcount 
FROM weather
WHERE events IS NULL
GROUP BY zip_code, events;


INSERT INTO #tblAll
SELECT zip_code, COUNT(*) AS Allcount 
FROM weather
GROUP BY zip_code, events;


SELECT a.zip_code, a.Allcount,n.Nullcount , n.Nullcount/a.Allcount
FROM #tblAll a
LEFT JOIN #tblNulls n ON a.zip_code = n.zip_code