CODEWITHSUNDEEP
sql
user979331
user979331

Reputation: 11891

SQL query works sometimes

I have this query and it seems to work most of the time, but I am still getting NULLs with it when I know it should return something,

I start with select, then this

(select sum(total) from orders
        where customer = 9
        and iscredit = 1
        and isdeleted = false and not id = 1560)

this peace of code gets me a total amount of orders for customer 9 and is not the current order, like a previous orders total...also notice that iscredit = 1 meaning that these are credit orders.

Next I want to add this query

(select sum(total) from orders
        where customer = 9
        and iscredit = 0
        and isdeleted = false and not id = 1560)

does the same thing but iscredit is 0 so orders that are not credits.

next I want to subtract what has been paid with this query

- (select ifnull(sum(p.amount), 0) from payment p
        inner join orders o
        on p.order = o.id
        where o.customer = 9) as previous

So all together this what my query looks like....

select 
(
    select sum(total) 
    from orders
    where customer = 9 and iscredit = 1 and isdeleted = false and not id = 1560
) 
+ 
(
    select sum(total) 
    from orders
    where customer = 9 and iscredit = 0 and isdeleted = false and not id = 1560
) 
- 
(
    select ifnull(sum(p.amount), 0) 
    from payment p
        inner join orders o
            on p.order = o.id
    where o.customer = 9
) as previous

Is there a better way to write this?

Any tips would be appreciated.

Thanks

Upvotes: 0

Views: 119

Answers (1)

David M
David M

Reputation: 72890

Something like this will lump the first two totals together.

(select sum(total)
from orders
where customer = 0
and (iscredit = 1 or iscredit = 0)
and isdeleted = false
and not id = 1560)

This will return null for no matching rows, so you might want to add:

(select ifnull(sum(total), 0)
from orders
where customer = 0
and (iscredit = 1 or iscredit = 0)
and isdeleted = false
and not id = 1560)

This is probably the source of your problems.

Upvotes: 2

Related Questions