conditional sql help

Question

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

Adriaan Stander · Accepted Answer · 2009-11-23 17:30:13Z

2

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

answered Nov 23, 2009 at 17:30

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ken White · Accepted Answer · 2009-11-23 17:41:11Z

2

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)

edited Nov 23, 2009 at 17:41

answered Nov 23, 2009 at 17:30

Ken White

126k15 gold badges237 silver badges476 bronze badges

10 Comments

Alex Over a year ago

Your code has invalid syntax: Must be "CASE WHEN PROCESSDATE BETWEEN ..." instead of "CASE PROCESSDATE WHEN BETWEEN ..."

gbn Over a year ago

SELECT NULL AS bar INTO #foo FROM sys.columns: what datatype is bar?

Ken White Over a year ago

@gbn: It's NULL. It has no datatype. NULL means "no known value". NULL <> NULL in a query, as one unknown can't be equivalent to another unknown (how could they be - they're unknown?).

gbn Over a year ago

@Ken: yes, correct, I know that. Now try it and report back ;-)

Ken White Over a year ago

@gbn: Perhaps you should try running "SELECT NULL + 1 AS bar" on SQL Server; add "FROM dual" for Oracle. What result do you get? I get an error message about the SELECT being invalid.

|

Collectives™ on Stack Overflow

conditional sql help

2 Answers 2

Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related