开发者

mysql count distinct join on two columns

开发者 https://www.devze.com 2023-02-22 00:39 出处:网络
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 combi

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
0

精彩评论

暂无评论...
验证码 换一张
取 消