sql - MySQL Iterate over column names -

i need in sql. :)

i have table :

column1   column2   column3   ... null       null       x         x           x         null x          null       null x          null       x ... 

i select count foreach column , have result order count:

column1    1034 column24   876 column3    567 ... 

for now, known how select column name :

select column_name information_schema.columns table_name = 'my_table'; 

and know how count in sql:

count(my_column); 

i know if possible in sql because need create view in phpmyadmin.

by way, forgive bad english! ;)

thanks lot take time me!

you want use union combine number of count queries, follows:

(select 'column1' `column_name`, count(column1) count my_table) union (select 'column2' `column_name`, count(column2) count my_table) -- ... order count desc 

if need build such query dynamically (e.g. because not know column names in advance¹) can in language of choice, using result of query on information schema.

if language of choice happens mysql, can use sql syntax prepared statements:

select concat(          group_concat('(            select ', quote(column_name), ' `column_name`,                   count(`', replace(column_name, '`', '``'), '` count              my_table          )'          separator ' union '        ), '        order count desc        ')   @sql   information_schema.columns  table_name = 'my_table';  prepare stmt @sql; execute stmt; deallocate prepare stmt; 

^{1. having dynamic schema of sort indicative of poor design.}