0

I have a large table in mysql with 150 columns (not my design)

and Im working on finding the population of each column (not empty string)

I have a table

email | fname | lname | ... | ... |
aaa   | bbb   | ccc   | rrr | qqq |
bbb   | eee   | ''    | ddd | bbb |
...   | ...   | ''    | ccc | eee |

desire result (format does not matter actually)

fields | # population     | % population
email  | 1501203          | 100
fname  | 1450000          | 95
lname  | ...              | ....
...

my code so far

SELECT 'email' as col1  ,COUNT(NULLIF( Email, '' )) as '#Populated'
                        , (COUNT(NULLIF( Email, '' ))  * 100/ (Select count(*) 
from mytable)) as '% pupolation'
FROM mytable
union 
SELECT 'fname'...
*repeat code above 150 times...*

is there a better way to do this?

i dont necessarily need to list the column names in 1 column, it just looks nicer

0

(COUNT(NULLIF( Email, '' )) --> COUNT(Email)

COUNT(col) is the same as COUNT(*) except for ignoring rows when col is NULL.

So..

SELECT
    COUNT(Email)              AS '#Email',
    COUNT(Email)*100/COUNT(*) AS '%Email',
    COUNT(fname)              AS '#fname',
    COUNT(fname)*100/COUNT(*) AS '%fname',
    ...

would give you 300 columns.

For 150 rows:

SELECT
    'Email'                   AS 'Column',
    COUNT(Email)              AS '# Populated',
    COUNT(Email)*100/COUNT(*) AS '% Populated'
    FROM tbl
UNION ALL
SELECT
    'fname',
    COUNT(fname),
    COUNT(fname)*100/COUNT(*)
    FROM tbl
UNION ALL
...

Notes:

  • The former uses one pass over the data; the latter needs 150 passes.
  • If you are using the commandline mysql, use \G to make the display more readable.
  • UNION defaults to UNION DISTINCT, which makes an extra pass over the result to de-dup. UNION ALL skips that; it is unnecessary in your case.
  • The AS is needed on only the first SELECT of a UNION. (If present on others, it is ignored.)

If you want to turn NULL into 0, be more efficient by doing the test outside the count:

    IFNULL(COUNT(Email), 0)              AS '# Populated',
    IFNULL(COUNT(Email), 0)*100/COUNT(*) AS '% Populated'

Other counting techniques:

SUM(Email != '')        -- excludes both NULLs and empty strings
SUM(Email IS NOT NULL)  -- excludes NULLs  (Same as COUNT(Email))
SUM(Email IS NULL)      -- includes only NULLs

A boolean expression evaluates to 0 or 1 (or NULL); SUM then adds up the ones.

  • thanks a lot! your code is 100x cleaner. I was running my older code before you posted and got an errorMsg 8115, Level 16, State 2, Line 2 probably because my table has 150 million rows...other than null value, i also need to find "not empty strings" for my case. So the code you provided actually gave me 100% population for each original column (because instead of "null" we just dont have anything in empty cells at all)...is there a way to fix that? – Chris K Nov 13 '17 at 23:57
  • did a quick fix and changed from COUNT() TO COUNT_BIG()...will see what happens tomorrow lol – Chris K Nov 14 '17 at 0:12
  • @ChrisK - I added some more. – Rick James Nov 14 '17 at 1:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.