mysql count distinct join on two columns
I am trying to count a distinct value in either of two rows. For instance, a table with columns fruit0, fruit1. I can get a count of the distinct values of either row, but I want a count of them combined (note this is a stupid contrived example).
Example:
id | fruit0 开发者_开发百科| fruit1
--------------------
0 | apple | banana
1 | apple | pear
2 | apple | apple
3 | pear | banana
I want something like:
fruit | count
--------------
apple | 4
banana| 2
pear | 2
select fruit_name, count(*)
FROM
(
SELECT fruit0 as fruit_name
FROM table1
UNION ALL
SELECT fruit1 as fruit_name
FROM table1
)aaa
GROUP BY fruit_name
精彩评论