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?
Advertisement
Answer
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;