conditional sql help
Trying to perform a conditional sql query. My form开发者_JS百科atting is incorrect. Any suggestions? Any help would be appreciated.
Select misc,
SUM(IF(processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000', getskusold.sprice, NULL) ) AS totalprice_date1,
SUM(IF(processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12-31 23:59:00.000', getskusold.sprice, NULL) ) AS totalprice_date2
from
misc_table
Rather try using a case statement
Select misc,
SUM(CASE WHEN processdate BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000' THEN getskusold.sprice ELSE 0 END ) AS totalprice_date1,
SUM(CASE WHEN processdate BETWEEN '2009-11-01 00:00:00.000' AND '2009-12-31 23:59:00.000' THEN getskusold.sprice ELSE 0 END ) AS totalprice_date2
from
misc_table
GROUP BY misc
You can't sum using NULL, as NULL isn't a numeric. Use 0 instead.
Don't use IF. Use CASE:
SUM(CASE WHEN PROCESSDATE BETWEEN '2009-08-01 00:00:00.000' AND '2009-10-31 23:59:00.000' THEN getskusold.sprice ELSE 0 END)
精彩评论