I have a table like the following table(id,a,b,c,d,e,…) Every row stores numbers and I want to know how often a specific element occurs using MySQL.

I can use the following: SELECT a, COUNT(a) FROM table GROUP BY a ORDER BY COUNT(a) DESC.

Now I want to do this for every single column). Is it possible to do this without just getting all one by one? Thanks for helping in the first place.

Example:

The result should be

Currents it’s just

by using SELECT id1, COUNT(id1) FROM table GROUP BY a ORDER BY COUNT(id1) DESC, id1 ASC because I’m just sorting for 1 column. How can I do this for multiple rows?

CodePudding user response：

When you do your group by you can either choose one column, as you have done, or you can choose more than one column in which case you will get the number of times each different combination of columns has occurred.
Does the following test schema make this clear?
Please be free to use the dbFiddle link at the bottom to test variations on the theme.

create table test(
a int,
b int);
insert into test values
(1,1),(1,1),(1,2),(2,2),(2,3),(2,4),(3,3),(3,4),
(1,1),(1,1),(1,2),(2,2),(2,3),(2,4),(3,3),(3,4),
(2,2),(2,3),(2,4),(2,2),(2,3),(2,2),(2,3),(2,4);

select a, count(a) from test group by a;
select b, count(b) from test group by b;

 a | count(a)
-: | -------:
 1 |        6
 2 |       14
 3 |        4

 b | count(b)
-: | -------:
 1 |        4
 2 |        7
 3 |        7
 4 |        6

select a, b, count(a) from test group by a,b;

 a |  b | count(a)
-: | -: | -------:
 1 |  1 |        4
 1 |  2 |        2
 2 |  2 |        5
 2 |  3 |        5
 2 |  4 |        4
 3 |  3 |        2
 3 |  4 |        2

db<>fiddle here

CodePudding user response：

Use UNION ALL for all the columns of the table so you get a single-column resultset and then aggregate:

SELECT id, COUNT(*) AS amount
FROM (
  SELECT id1 AS id FROM tablename UNION ALL
  SELECT id2 FROM tablename UNION ALL 
  SELECT id3 FROM tablename 
) t
GROUP BY id;