VaN VaN - 1 year ago 129
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download