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;