CODEWITHSUNDEEP
mysqlsql
dv02
dv02

Reputation: 342

How to get percentage of rows which are not NULL in a specific column?

I have a database setup like this:

Table called reviews

+ -------- +
|  review  |
+ -------- +
|   awda   |
|   ggagw  |
|   okok   |
|   ligjr  |
|   kkfm   |
|   seff   |
|   oawr   |
|   abke   |
|  (null)  |
|  (null)  |
|  (null)  |
|  (null)  |
|  (null)  |
|  (null)  |
|  (null)  |
+ -------- +

How do I get the percentage of how many rows there are, which are NOT NULL?

A basic "formula" of what I want:

percentage = 100 * ( (Sum of rows where review is not null) / (Amount of rows) )

For the example above, this would be: percentage = 100 * ( ( 8 ) / ( 15) ) = 53.33333333

How can I achieve that by using only one MySQL query?

Upvotes: 3

Views: 1757

Answers (2)

Jadson Santos
Jadson Santos

Reputation: 319

-- now for finished 
-- Calculate the percentage with the formula
-- 100 * value / (total)
select _not_null, _null, percentage FROM (
    select _not_null, _null, (100 * _not_null / ( _not_null + _null)) as percentage FROM (
        -- it starts here !!!
        -- sum 1 for each time the column in null 
        -- and 0 for each time the column is not null
        select some_column, 
        sum( CASE WHEN review is not null THEN 1 ELSE 0 END) as _not_null,
        sum( CASE WHEN review is null THEN 1 ELSE 0 END) as _null
        from my_table 
        group by some_column
    ) as internal
) as internal2
--
-- you can select values over a percentage yet
--
where internal2.percentage > 75

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I think the simplest way is:

select avg( review is not null ) * 100
from reviews;

MySQL treats boolean expressions as numbers in a numeric context, with 0 for false and 1 for true.

Similar method does the explicit division:

select 100*count(review) / count(*)
from reviews;

Upvotes: 8

Related Questions