I need your help in SQL. :)
I have a table which look like that :
Column1 Column2 Column3 ...
null null x
x x null
x null null
x null x
WHERE table_name = 'my_table';
You want to use
UNION to combine a number of your count queries, as follows:
(SELECT 'Column1' AS `COLUMN_NAME`, COUNT(Column1) AS Count FROM my_table) UNION ALL (SELECT 'Column2' AS `COLUMN_NAME`, COUNT(Column2) AS Count FROM my_table) -- ... ORDER BY Count DESC
If you need to build such a query dynamically (e.g. because you do not know the column names in advance1) then you can do so in your language of choice, using the result of your query on the information schema.
If your language of choice happens to be MySQL, then you can use its SQL syntax for prepared statements:
SELECT CONCAT( GROUP_CONCAT('( SELECT ', QUOTE(COLUMN_NAME), ' AS `COLUMN_NAME`, COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '` AS Count FROM my_table )' SEPARATOR ' UNION ALL ' ), ' ORDER BY Count DESC ') INTO @sql FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_table'; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
1. Having dynamic schema of this sort is generally indicative of poor design.