CODEWITHSUNDEEP
mysqlsqlselectgroup-bymultiple-columns
VaN
VaN

Reputation: 2210

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?

Upvotes: 1

Views: 1146

Answers (1)

Mureinik
Mureinik

Reputation: 312267

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

Upvotes: 2

Related Questions