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;