Guillaume Brunson Guillaume Brunson - 6 months ago 25
SQL Question

MySQL Iterate over column names

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
...


I would like select the count foreach column and have a result like this order by this count:

Column1 1034
Column24 876
Column3 567
...


For now, I known how to select my column name :

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'my_table';


And I know how to count in SQL:

COUNT(my_column);


I would like to know if it is possible to do that in SQL because I need to create a view in phpmyadmin.

By the way, forgive my bad english! ;)

Thanks a lot to those who take a time to help me!

Answer

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.

Comments