CODEWITHSUNDEEP
sql
Razeru
Razeru

Reputation: 1

Get sum of a selection with two tables?

I'm trying to get the sum of a column if their ISBN is the same and the shipdate is null. Shipdate is in a different table so it seems that " and shipdate is null" portion does not properly get checked, in fact they all seem to be counted as not null which is not true.

select oh.OrderNo, shipdate, qty, ISBN,
    need = (select sum(qty) 
    from OrderDetail 
    where od.ISBN = ISBN and shipdate is null)
from orderheader oh
join orderdetail od on oh.OrderNo=od.OrderNo
where shipdate is null

I've tried another method

need = (select sum(case when shipdate is null then qty else 0 end) 
from OrderHeader where shipdate is null and od.ISBN = ISBN),

but it has an outer reference so it won't work. Any help is greatly appreciated.

Edit: Sample

OrderNo shipdate    qty ISBN        need
7       NULL        1   0030615372  1
7       NULL        1   0345350499  11
9       NULL        1   0345350499  11
10      NULL        4   0345350499  11
10      NULL        2   0395851580  8
10      NULL        3   0395851580  8
7       NULL        1   0439136350  9
7       NULL        1   0887251714  1

need is wrong, it is including shipdates which are not null (which aren't shown) as well. need should be: 1 6 6 6 5 5 1 1, essentially the sum of the qty for where the ISBN is the same

Upvotes: 0

Views: 33

Answers (1)

AdamMc331
AdamMc331

Reputation: 16690

Instead of using a subquery to get the sum, just use the SUM() aggregate function. This will work assuming you join the tables properly. Without seeing your table designs and some sample data from both it's hard to test, but I picture something like this:

SELECT oh.orderno, od.shipdate, od.isbn, SUM(od.qty) AS need
FROM orderdetail od
JOIN orderheader oh ON oh.orderno = od.orderno
WHERE od.shipdate IS NULL
GROUP BY isbn

You need to group by the ISBN in order to get the proper sum for that ISBN. That may be part of the problem you were having.

Upvotes: 1

Related Questions