How to get count of distinct values from two columns?

Question

I have a table like below and I want to count distinct values from two columns (fruit1 and fruit2)

+------+--------+--------+--------+------+
| s_id | s_name | fruit1 | fruit2 | rs   |
+------+--------+--------+--------+------+
|    1 | ram    | apple  | mango  |   20 |
|    2 | raj    | apple  | banana |   13 |
|    3 | aman   | orange | banana |    7 |
|    4 | mangal | orange | apple  |   16 |
|    5 | ravi   | apple  | banana |   17 |
|    6 | pawan  | apple  | apple  |    9 |
|    7 | shyam  | apple  | orange |   11 |
+------+--------+--------+--------+------+

I am able to do this for one column using below command, but not success for two column, not getting how to achieve this.

select fruit1, count(*) as count from s_info group by fruit1;

which results in

+--------+-------+
| fruit1 | count |
+--------+-------+
| apple  |     5 |
| orange |     2 |
+--------+-------+

But below is my expected result, which I am not getting.

+--------+-------+
| fruits | count |
+--------+-------+
| apple  |     7 |
| orange |     3 |
| banana |     3 |
| mango  |     1 |
+--------+-------+

Gordon Linoff · Accepted Answer · 2017-03-31 11:42:38Z

4

Use union all before doing the final aggregation:

select fruit, count(*)
from ((select fruit1 as fruit from s_info) union all
      (select fruit2 as fruit from s_info)
     ) i
group by fruit;

answered Mar 31, 2017 at 11:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get count of distinct values from two columns?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related