How to see percentage of existing values for all columns in mysql table?

Question

I have three large-ish tables (~20 columns each) and I want to know what percentage of rows have a value for each column.

For instance, this table:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
6716 |  Yummy Molasses ... | 1 cookie    |
3765 |  Rosemary-Red Wi... |             |
5178 |  Stuffed Avocado... |             |
6025 |  Amazing Pea Sou... | about 1 cup |
4412 |  Overnight Oatme... | 1 cup       | Note: Steel-cut oats...

Might yield results similar to this:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
100% | 100%                | 60%         | 20%

This is for an internal report, so the format isn't too important. If I only have the count() instead of the percent, that's fine. Having the column name would be nice, but not necessary. Etc.

I'm not very familiar with queries that involve more meta questions like gathering table names or working over all the columns in a table, so I'm interested to see what people suggest.

Community · Accepted Answer · 2017-04-13 12:43:00Z

I have a Dynamic SQL solution

PROPOSED QUERY

SET group_concat_max_len = 1048576;
SET @GivenDB = 'mydb';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
')*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

EXAMPLE

Let's use a sample table I made up for another question

mysql> use timyash
Database changed
mysql> show tables;
+-------------------+
| Tables_in_timyash |
+-------------------+
| mytable           |
+-------------------+
1 row in set (0.00 sec)

mysql> desc mytable;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| id           | int(11) | NO   | PRI | NULL    | auto_increment |
| transmitted  | int(11) | NO   |     | NULL    |                |
| connect_time | int(11) | NO   |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |        78073 |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |       596408 |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY DISPLAYED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT COUNT(id)*100/COUNT(1) id_pct,COUNT(transmitted)*100/COUNT(1) transmitted_pct,COUNT(connect_time)*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.02 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |         100.0000 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED WITH NULL DATA

Let's change two rows to have NULL column data

mysql> alter table mytable modify column connect_time INT NULL;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update mytable set connect_time = 1/0 WHERE id in (2,5);
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from mytable;
+----+-------------+--------------+
| id | transmitted | connect_time |
+----+-------------+--------------+
|  1 |     2650131 |       117987 |
|  2 |     6465178 |         NULL |
|  3 |    25905117 |       159268 |
|  4 |    59178089 |       410282 |
|  5 |    73502942 |         NULL |
|  6 |    75695427 |       683045 |
|  7 |    77576167 |       740379 |
+----+-------------+--------------+
7 rows in set (0.00 sec)

mysql>

Let's run it again

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
    -> ')*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-29 15:25 EST

In response to your last comment, use this version

SET group_concat_max_len = 1048576;
SET @GivenDB = 'timyash';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',
GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
SELECT @sql\G
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

You do not need to check the datatype with this one.

To show you that it works ...

mysql> SET group_concat_max_len = 1048576;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenDB = 'timyash';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @GivenTable = 'mytable';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT('SELECT ',
    -> GROUP_CONCAT(CONCAT('SUM(IF(IFNULL(',column_name,
    -> ','''')='''',0,1))*100/COUNT(1) ',column_name,'_pct')),
    -> ' FROM ',table_schema,'.',table_name) sqlstmt
    -> INTO @sql FROM information_schema.columns
    -> WHERE table_schema=@GivenDB
    -> AND table_name=@GivenTable;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT @sql\G
*************************** 1. row ***************************
@sql: SELECT SUM(IF(IFNULL(id,'')='',0,1))*100/COUNT(1) id_pct,SUM(IF(IFNULL(transmitted,'')='',0,1))*100/COUNT(1) transmitted_pct,SUM(IF(IFNULL(connect_time,'')='',0,1))*100/COUNT(1) connect_time_pct FROM timyash.mytable
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+----------+-----------------+------------------+
| id_pct   | transmitted_pct | connect_time_pct |
+----------+-----------------+------------------+
| 100.0000 |        100.0000 |          71.4286 |
+----------+-----------------+------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Very cool - thanks! It looks like there's a typo in your query PREPARE s FROM @sql; EXECUTE s\G DEALLOCATE PREPARE s;. That \G should be a semicolon, right? — doub1ejack, Commented Dec 29, 2014 at 19:52
It looks like it should be possible to also exclude empty text fields, but I haven't figured that out yet.. — doub1ejack, Commented Dec 29, 2014 at 20:06
You might be able to get away with replacing COUNT(',column_name,') with something like SUM(IF(IFNULL(',column_name,','''')='''',0,1)) without having to check the datatype beforehand. — RolandoMySQLDBA, Commented Dec 29, 2014 at 20:17

Connor · Accepted Answer · 2014-12-29 17:41:25Z

You can hardcode this as SQL, though it might be tedious. For your example table, you would use the following:

SELECT (COUNT(id) / COUNT(*)) * 100 AS id, (COUNT(title) / COUNT(*)) * 100 AS title, (COUNT(serving) / COUNT(*)) * 100 AS serving, (COUNT(note) / COUNT(*)) * 100 AS note FROM <table_name>

Note that this will return the percentage of rows in each column that are NOT NULL out of the total rows. This means that if you have a column with empty data, such as blank spaces, it will not be considered NULL, and will thus be counted in the percentage.

Lennart - Slava Ukraini · Accepted Answer · 2014-12-29 17:24:02Z

Assuming you have a host-language such as php, python or perl you can use the information_schema.columns to get column_names for the tables at stake. You can then generate queries like:

select cast((100.0*count(ID) / count(1)) as decimal(3,0)) AS ID
     , cast((100.0*count(TITLE) / count(1)) as decimal(3,0)) AS TITLE
     , cast((100.0*count(SERVING) / count(1)) as decimal(3,0)) AS SERVING
     , cast((100.0*count(NOTE) / count(1)) as decimal(3,0)) AS NOTE
from T;

from there. Dependent on how clever the optimizer is count(1) may be evaluated more than once. Check with explain

Stack Exchange Network

How to see percentage of existing values for all columns in mysql table?

3 Answers 3

PROPOSED QUERY

EXAMPLE

PROPOSED QUERY DISPLAYED

PROPOSED QUERY EXECUTED

PROPOSED QUERY EXECUTED WITH NULL DATA

GIVE IT A TRY !!!

UPDATE 2014-12-29 15:25 EST

Your Answer

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

Hot Network Questions

How to see percentage of existing values for all columns in mysql table?

3 Answers 3

PROPOSED QUERY

EXAMPLE

PROPOSED QUERY DISPLAYED

PROPOSED QUERY EXECUTED

PROPOSED QUERY EXECUTED WITH NULL DATA

GIVE IT A TRY !!!

UPDATE 2014-12-29 15:25 EST

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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