VaN VaN - 10 days ago 4
MySQL Question

mysql group by 2 columns on same value

I have the following table :

ID custom_1 custom_2
+----+------------------------+------------------------
1 value_1 NULL
2 value_2 value_1
3 value_3 value_4
4 NULL value_4


I want to build a query to fetch the following output :

value count
+------------------------+------------------------
value_1 2
value_2 1
value_3 1
value_4 2


I know I can GROUP BY several fields, but I think this is not the way to do it in that case. Do I have to use a UNION?

Answer

You could use the union all operator to get both columns in a single result column, and then group by it:

SELECT   custom, COUNT(*)
FROM     (SELECT custom_1 AS custom
          FROM   mytable
          UNION ALL
          SELECT custom_2 AS custom
          FROM   mytable) t
GROUP BY custom