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%
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,'')),
' 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;

