how can I get the total count of same name of different columns in one table in MySQL?

user_10223227 0
Viewed 327

Here is the example of my question.

enter image description here

In the ID table, there are two columns, one is id1 and another is id2.

I just want to know the total id count in id1 and id2 columns, like the right table.

In that situations, How can I get the total count of same name of different columns in one table in MySQL code?

count select mysql sql
1 Answers

You could use union all to generate a single column with all the IDs, and then count them:

SELECT   id, COUNT(*) 
FROM     (SELECT id1 AS id FROM mytable
          UNION ALL
          SELECT id2 AS id FROM mytable) t
GROUP BY id
user_24227760
Related