CODEWITHSUNDEEP
mysql
Rébecca
Rébecca

Reputation: 31

How to Count Distinct for two columns in MYSQL

I have a table with Entries from Participants with multiples Codes and I want to group them by how many Participants used how many distinct Codes.

This is my table

| CodeID | ClientID |
--------------------
|   1    |    36    |
|   1    |    36    |
|   2    |    36    |
|   3    |    36    |
|   10   |    36    |
|   9    |    36    |
|   3    |    36    |
|   2    |    36    |
|   1    |    38    |
|   1    |    39    |
|   1    |    40    |
|   2    |    40    |
|   3    |    40    |
|   1    |    41    |
|   2    |    41    |

I tried with Group By and I have half the result I'm looking for, this:

SELECT COUNT(DISTINCT CodeID) AS Codes, ClientID FROM Entry GROUP BY ClientID ORDER BY Codes

gives me this

| Codes | ClientID |
--------------------
|   1   |    38    |
|   1   |    39    |
|   2   |    41    |
|   3   |    40    |
|   5   |    36    |

And the result I'm looking for is this:

| Codes | Clients |
-------------------
|   1   |    2    |
|   2   |    1    |
|   3   |    1    |
|   5   |    1    |

I don't know if there is a way to do this with multiples GROUP BY or with a subquery...

Upvotes: 0

Views: 28

Answers (3)

Rébecca
Rébecca

Reputation: 31

With your answers I edited my code to make it work, thanks a lot!

SELECT Codes, COUNT(ClientID) AS Clients 
    FROM ( SELECT COUNT(DISTINCT CodeID) AS Codes, ClientID 
           FROM Entry 
           GROUP BY ClientID) Result 
    GROUP BY Codes

Upvotes: 0

Sal
Sal

Reputation: 1317

With one more GROUP BY.

SELECT cnt, count(CodeID)
  FROM ( SELECT CodeID, count(distinct ClientID) cnt
           FROM Entry
           GROUP BY CodeID
       ) T
  GROUP BY cnt

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

If you want the number of clients for each code id You should do the inverse 

    SELECT CodeID codes , COUNT(DISTINCT ClientID) clients 
    FROM Entry 
    GROUP BY codes ORDER BY Codes

Upvotes: 2

Related Questions