CODEWITHSUNDEEP
mysql
dCoder
dCoder

Reputation: 67

Count unique values from duplicates

I have following data on the table.

Uid | comm | status
-------------------
12    23     eve
15    23     eve
20    23     mon
12    23     mon
20    23     eve
17    23     mon

how do i query to get below result to avoid duplicates and make sure if i count uid for "eve" and same uid appears on "mon" then count only uid for "eve"?

count | status
-------------------
3         eve
1         mon

Thanks for the help!

Upvotes: 0

Views: 33

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query in order to pick each Uid value once:

SELECT Uid, MIN(status)
FROM mytable
GROUP BY Uid

Output:

Uid MIN(status)
---------------
12  eve
15  eve
17  mon
20  eve

Using the above query you can get at the desired result like this:

SELECT status, count(*)
from (
   SELECT Uid, MIN(status) AS status
   FROM mytable
   GROUP BY Uid ) AS t
GROUP BY status

Demo here

Upvotes: 2

Related Questions