Recently I needed to count the instances of communications that were recorded to a DB between the lefties, and the righties. The conversation was over 100 responses long in one conversation, and a simple SQL Count showed me that. But I wanted a little more detail.
select count(*) as TotalCount from THREADS where conversation_id = ‘123’
TotalCount = 100
I wanted to know who had more input to the conversation, but needed to know a simple query to determine the author of the thread counts in one simple query. I found a rather simple solution to this topic!
count (case when user = ‘lefties’ then 1 end) as LeftiesCount,
count (case when user = ‘righties’ then 1 end) as RightiesCount
from THREADS where conversation_id = ‘123’
LeftiesCount = 63
RightiesCount = 37
With the case we say, user = lefties/righties add 1 to the count. We have counts for the threads and the two cases.