CODEWITHSUNDEEP
mysqlsqldatabase
Hafiz Muhammad Shafiq
Hafiz Muhammad Shafiq

Reputation: 8670

mysql get distinct value over two columns

I have a table with data similar to below table

id  c1  c2

1   a   b
2   e   f
3   b   d
4   x   e

I want to get distint values over two column and their sum also. My expected output is

a   1
b   2
d   1
e   2
f   1
x   1

First column is overall distinct values and second column is time of occurance. How do I get it in MYSQL ?

Upvotes: 0

Views: 41

Answers (2)

Krishnakumar
Krishnakumar

Reputation: 745

You can do this by creating temporary table:

From your example:

select * from Table1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | b    |
|    2 | e    | f    |
|    3 | b    | d    |
|    4 | x    | e    |
+------+------+------+

Create a temp table:

create temporary table dummy (col1 text);

Insert into TEMP table values from your Table:

insert into dummy select col2 from Table1;

insert into dummy select col3 from Table1;

Now the TEMP table:

select * from dummy;
+------+
| col1 |
+------+
| a    |
| e    |
| b    |
| x    |
| b    |
| f    |
| d    |
| e    |
+------+

Now your Expected Result:

select distinct col1 , count(*) from dummy group by col1;
+------+----------+
| col1 | count(*) |
+------+----------+
| a    |        1 |
| b    |        2 |
| d    |        1 |
| e    |        2 |
| f    |        1 |
| x    |        1 |
+------+----------+

Upvotes: 0

Thomas G
Thomas G

Reputation: 10206

As jarlh said in the comments, you can do this with a UNION ALL subquery :

SELECT col, COUNT(*)
FROM
(
    SELECT c1 AS col FROM thetable
    UNION ALL
    SELECT c2 AS col FROM thetable
) T
GROUP BY col

Upvotes: 1

Related Questions