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]
