4

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.

2

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 Dec 29 '14 at 19:52
  • Fixed typo, thanks. That was left over from my testing. – RolandoMySQLDBA Dec 29 '14 at 20:01
  • It looks like it should be possible to also exclude empty text fields, but I haven't figured that out yet.. – doub1ejack Dec 29 '14 at 20:06
  • Do you mean non-NULL empty strings ? – RolandoMySQLDBA Dec 29 '14 at 20:07
  • 1
    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 Dec 29 '14 at 20:17
1

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.

0

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

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.