SQL and NULLs

I am having trouble creating a report because I have nulls pop up in the tables I'm using.

If I have a query that takes sum(quantite * price * discount)
and the discount is null I get null as the sum.

Is there any way to take the greater of 2 values ie
select greaterOf(discount, 1)?

thanks
Ben Martin

Comments

  • : I am having trouble creating a report because I have nulls pop up in the tables I'm using.
    :
    : If I have a query that takes sum(quantite * price * discount)
    : and the discount is null I get null as the sum.
    :
    : Is there any way to take the greater of 2 values ie
    : select greaterOf(discount, 1)?
    :
    : thanks
    : Ben Martin
    :

    I'm not sure of the syntax (my SQL's a little rusty ;-)), but you could use something like this:

    SELECT sum(quantite * price * discount) WHERE discount = 0
    LEFT JOIN
    sum(quantite * price) WHERE discount > 0

    I think it's something like that, asuming that discount >= 0

  • : I am having trouble creating a report because I have nulls pop up in the tables I'm using.
    :
    : If I have a query that takes sum(quantite * price * discount)
    : and the discount is null I get null as the sum.
    :
    : Is there any way to take the greater of 2 values ie
    : select greaterOf(discount, 1)?
    :
    : thanks
    : Ben Martin
    :

    I'm not sure of the syntax (my SQL's a little rusty ;-)), but you could use something like this:

    SELECT sum(quantite * price * discount) WHERE discount = 0
    LEFT JOIN
    sum(quantite * price) WHERE discount > 0

    I think it's something like that, asuming that discount >= 0


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

In this Discussion