Get percent of columns that completed by calculating null values

Question

I have a table with a column that allows nulls. If the value is null it is incomplete. I want to calculate the percentage complete.

Can this be done in MySQL through SQL or should I get the total entries and the total null entries and calculate the percentage on the server?

Either way, I'm very confused on how I need to go about separating the variable_value so that I can get its total results and also its total NULL results.

SELECT
    games.id
FROM 
    games
WHERE 
    games.category_id='10' AND games.variable_value IS NULL

This gives me all the games where the variable_value is NULL. How do I extend this to also get me either the TOTAL games or games NOT NULL along with it?

Table Schema:

id (INT Primary Auto-Inc)

category_id (INT)

variable_value (TEXT Allow Null Default: NULL)

Answer 1

When you use "Count" with a column name, null values are not included. So to get the count or percent not null just do this...

SELECT
   count(1) as TotalAll,
   count(variable_value) as TotalNotNull,
   count(1) - count(variable_value) as TotalNull,
   100.0 * count(variable_value) / count(1) as PercentNotNull
FROM
   games
WHERE
   category_id = '10'

Answer 2

SELECT
    SUM(CASE WHEN G.variable_value IS NOT NULL THEN 1 ELSE 0 END)/COUNT(*) AS pct_complete
FROM
    Games G
WHERE
    G.category_id = '10'

You might need to do some casting on the SUM() so that you get a decimal.

Answer 3

To COUNT the number of entries matching your WHERE statement, use COUNT(*)

SELECT COUNT(*) AS c FROM games WHERE games.variable_value IS NULL

If you want both total number of rows and those with variable_value being NULL in one statement, try GROUP BY

SELECT COUNT(variable_value IS NULL) AS c, (variable_value IS NULL) AS isnull FROM games GROUP BY isnull

Returns something like

c   |  isnull
==============
12  |  1
193 |  0

==> 12 entries have NULL in that column, 193 havn't

==> Percentage: 12 / (12 + 193)