CODEWITHSUNDEEP
sqlsql-server
jeff
jeff

Reputation: 13

conditional sql help

Trying to perform a conditional sql query. My formatting 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

Upvotes: 0

Views: 88

Answers (2)

Ken White
Ken White

Reputation: 125620

  1. You can't sum using NULL, as NULL isn't a numeric. Use 0 instead.

  2. 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)

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166336

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

Upvotes: 2

Related Questions