I would think that the Jan solution should execute in a single pass through the table. The Dave (subquery) version will probably need to make 3 passes through the table get the same results.
As was mentioned in another response, the inequality comparisons (=,<,>,etc.) are much faster than a LIKE comparison. So, I would also suggest simplifying Jan's solution to read " IF(status='xxx',1,0)" instead of "IF(concat(....) LIKE ...,1,0) ". Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jerry Swanson <[EMAIL PROTECTED]> wrote on 01/13/2005 07:56:18 AM: > What query suppose to be faster? Jan or Dave? > > Jan Solution: > SELECT > date, > SUM(IF(CONCAT(status,id) LIKE 'received%',1,0)) as received, > SUM(IF(CONCAT(status,id) LIKE 'send%',1,0)) as send, > SUM(IF(CONCAT(status,id) LIKE 'cancelled%',1,0)) as cancelled > FROM > account > GROUP BY date; > > > On Wed, 12 Jan 2005 22:51:00 -0500, Dave Merrill <[EMAIL PROTECTED]> wrote: > > > I have table account (see below). I need to get count of received, > > > count of send and cound of cancelled records. I know that I can do > > > this in 3 queries. > > > #1. select count(*) from account where status='received'; > > > #2. select count(*) from account where status='send'; > > > #3. select count(*) from account where status='cancelled'; > > > > > > Is it possible to get count of each status in one query instead of 3? > > > > How about this: > > > > select > > (select count(*) from account where status = 'received') as > > count_received, > > (select count(*) from account where status = 'send') as count_send, > > (select count(*) from account where status = 'cancelled') as > > count_cancelled > > > > You could also build a stored proc that looped over a list of the values to > > find, or, I think maybe, over the actual distinct values in the field. Not > > so sure about that part. > > > > Dave Merrill > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
