What is the difference between '='' and '<>'? TH
On Thu, 13 Jan 2005 09:13:07 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
