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.

More of simple queries

You can get the same results as distinct with Count, but Count gives you more value.

Here’s Why!

Consider the following table

x    y    z
------------
a    b    c
a    b    e
a    b    c

If you were to perform a simple query to get the distinct records, you will get 2 results in form the query. In effect, you would receive rows 1 and 2 and the third would be omitted because it was the same as row 1.

The results of the simple distinct query are:

 x   y  z
----------
 a   b   c
 a   b   e

To get more value from the query using Count instead of Distinct write the following query.

select count(*) as count, x,y,z from TableA group by x, y, z

The results you will get will look like this.

count  x   y  z
----------------
 2     a   b   c
 1     a   b   e

Now in addition to having the distinct rows found by your query, you have the count of the distinct rows. I like to order by count descending, this way I know what might be the most important.

I found this on SmashingHub.com and it shows the power of simple Queries!

http://smashinghub.com/handy-php-and-mysql-queries-for-developers.htm