select psDealerID, sum(if(substring(updated, 1, 10) = curdate(), 1, 0)), sum(if(substring(updated, 1, 10) = date_sub(curdate(), interval 1 day), 1, 0)) from provision group by psDealerID
I love crosstab queries, but one thing really eats at me. I'd like to be able to add significance to the AS with a function. So that the return would look something like; +------------+------------+------------+ | psDealerID | 2006-06-07 | 2006-06-06 | +------------+------------+------------+ | | 0 | 4 | | 301AA | 0 | 0 | | 301AB | 2 | 0 | | 302AA | 0 | 0 | | 303AA | 0 | 1 | We all know that you cannot do something like this; sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS curdate() nor can you use user variables even though they get set properly set @d1 = curdate(); sum(if(substring(updated, 1, 10) = curdate(), 1, 0)) AS @d1 So, does anyone know of a work around? I have RTFM and STFW...but to no avail. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
