I'm a MySQL newbie, though I've worked with SQL Server and other RDBMS for a long time. That said, I'm *guessing* that the stored proc solution would be the fastest, since its execution plan is precomputed, but a quick look seems to say that stored procs are only available in v5, still in alpha.
Of the two queries, I don't know. If this was SQL Server, I'd suggest comparing them in the Query Profiler, with your actual data. That would give you a look at the execution plan for each method, with the "cost" of each, broken out into component parts. Is there a similar facility in MySQL, or some add-on to it? I looked at EXPLAIN, but it doesn't seem to show costs. And bottom line (you knew I'd say this, right?), Try It! With your real data. Dave Merrill > 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 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]
