Counting More than One Criteria

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!

select
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.

Leave a Reply

Your email address will not be published. Required fields are marked *