CODEWITHSUNDEEP
mysqlsql
Mohanad Shamsneh
Mohanad Shamsneh

Reputation: 49

Count distinct value of column regardless change in other columns

I have the following table:

+---------+---------+--------+------+
| Whisper | Client  | Tenant | Ring |
+---------+---------+--------+------+
| W1      | iOS     | T3     | R1   |
+---------+---------+--------+------+
| W2      | iOS     | T2     | R1   |
+---------+---------+--------+------+
| W1      | Android | T3     | R1   |
+---------+---------+--------+------+
| W2      | Android | T2     | R1   |
+---------+---------+--------+------+
| W3      | Android | T4     | R2   |
+---------+---------+--------+------+

And I want to count distinct Whispers knowing that there is a change in client column, currently I'm using this:

WhipserCounts =
SELECT COUNT(DISTINCT Whipser) AS Whispers,
                Client,
                Tenant,
                Ring
FROM InputData
GROUP BY Client, Tenant, Ring;

But I see that W1 and W2 are counted twice for each client but I need to consider them as one no matter the client is, so for the first time If I got iOS I need to set the client by the first value. (Knowing that I need the client in the output table in the same time).

What I got:

+----------+---------+--------+------+
| Whispers | Client  | Tenant | Ring |
+----------+---------+--------+------+
| 1        | iOS     | T3     | R1   |
+----------+---------+--------+------+
| 1        | iOS     | T2     | R1   |
+----------+---------+--------+------+
| 1        | Android | T3     | R1   |
+----------+---------+--------+------+
| 1        | Android | T2     | R1   |
+----------+---------+--------+------+
| 1        | Android | T4     | R2   |
+----------+---------+--------+------+

What I need:

+----------+---------+--------+------+
| Whispers | Client  | Tenant | Ring |
+----------+---------+--------+------+
| 1        | iOS     | T3     | R1   |
+----------+---------+--------+------+
| 1        | iOS     | T2     | R1   |
+----------+---------+--------+------+
| 1        | Android | T4     | R2   |
+----------+---------+--------+------+

Could you please help with this?

Upvotes: 0

Views: 114

Answers (1)

Jonathan Delean
Jonathan Delean

Reputation: 1084

To do that, you need to use MAX(), not group by :

SELECT COUNT(DISTINCT Whipser) AS Whispers,
                MAX(Client),
                Tenant,
                Ring
FROM InputData
GROUP BY Tenant, Ring;

Upvotes: 1

Related Questions