Ian Sales wrote:
- I always concatenate the wanted criteria (status, in this case) with a unique ID field in order to be absolutely certain I'm counting every instance of each value of status. I've not investigated to see whether I *really* need to do this, but it feels safer...
- ian
Then that's a habit you should lose. The SUM(IF...) looks at each *row*, not each value. Let's take a look at your query (I'm leaving out the 'GROUP BY date', as the OP didn't ask for that.):
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;The CONCAT adds the id to the end of the status string, but your LIKE matches strings with the correct beginning. Do you see? For a given row, status either starts with 'send' or it doesn't. Adding the id to the end of the string cannot change that. You are making mysql do extra, unnecessary work to no effect. You'll get the correct result, but it will take longer than it should.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
