SQL-Doesn't return a value when the columns are multiplied.

Question

I have 3 columns in my db table, I need to multiply column, itm_count & itm_price and output the total for a given id(itm_id).

SELECT sum(itm_price * itm_count) as total FROM ods_temporder WHERE itm_id='11'

I tried to do this using the above sql query, but the result was null. What seems to be the issue here?

Answer 1

What do this give you?

SELECT itm_price, itm_count FROM ods_temporder WHERE itm_id='11'

Is either of itm_price or itm_count NULL? Do you have rows for itm_id = 11?

Answer 2

What does itm_price and itm_count contain when itm_id= 11? Is this SQL Server? If so you can use ISNULL to handle whether itm_price or itm_count is null

Answer 3

Try:

SELECT SUM(COALESCE(itm_price, 0) * COALESCE(itm_count, 0)) as total 
  FROM ods_temporder 
 WHERE itm_id='11'

COALESCE is an ANSI standard for dealing with NULL values - if itm_price or itm_count is null, zero will be used in this case as the value. Otherwise, either you don't have a row with a value of 11 for the itm_id column, or you're looking for the 11 value in the wrong column.