Geek Answers Handbook

Get percentages of columns filled by calculating null values

I have a table with a column that allows null. If null, it is incomplete. I want to calculate the percentage of completion.

Is it possible to do this in MySQL via SQL, or should I get the total records and total zero records and calculate the percentage on the server?

In any case, I am very confused about how I need to separate the value_value so that I can get its final results, as well as its final NULL results.

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

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

Table layout:

id (INT Primary Auto-Inc)

category_id (INT)

variable_value (TEXT Allow Null Default: NULL)

+5
3

"Count" , . , null, ...

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'
+11
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'

, SUM(), .

+5

, WHERE, COUNT (*)

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

, _ NULL , GROUP BY

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

-

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

== > 12 NULL , 193 havn't

== > : 12/(12 + 193)

+2

More articles:


All Articles