0

I have a item table from which i want to get Sum of item quantity

Query:

Select item_id, Sum(qty) from item_tbl group by item_id

Result:

==================
| ID  | Quantity  |
===================
| 1   | 10        |
| 2   | 20        |
| 3   | 5         |
| 4   | 20        |

The second table is invoice table from which i am getting the item quantity which is sold. I am joining these two tables as

Query:

Select item_tbl.item_id, Sum(item_tbl.qty) as [item_qty], 
-isnull(Sum(invoice.qty),0) as [invoice_qty] 
from item_tbl 
left join invoice on item_tbl.item_id = invoice invoice.item_id group by item_tbl.item_id

Result:

=================================
| ID  | item_qty  | invoice_qty |
=================================
| 1   | 10        |  -5          |
| 2   | 20        |  -20         |
| 3   | 10        |  -25         |   <------ item_qty raised from 5 to 10 ?? 
| 4   | 20        |  -20         |

I don't know if i am joining these tables in right way. Because i want to get everything from item table and available things from invoice table to maintain the inventory. So i use left join. Help please..

Modification

when i added group by item_id, qty i got this:

=================================
| ID  | item_qty  | invoice_qty |
=================================
| 1   | 10        |  -5          |
| 2   | 20        |  -20         |
| 3   | 5         |  -5          | 
| 3   | 5         |  -20         |
| 4   | 20        |  -20         |

As its a view so ID is repeated. what should i do to avoid this ??

Improve this question
12
  • Is it possible you have 2 records for Item 3 in one of those tables? Seems strange that it would effect only one Item ID, so there must be something unique about it. Commented Nov 23, 2015 at 20:00
  • 2
    If you have 2 invoices for id = 3 - then the join will create 2 rows in the result instead of the expected one line, making the sum double. you should have the join over a unique result set, possibly by using a subquery that groups the invoices prior to the join you've done. Commented Nov 23, 2015 at 20:01
  • @Ron.B.I has the real issue. The solution is pretty simply though (assuming that item_tbl.ID is unique), just add item_tbl.qty to your group by clause. Commented Nov 23, 2015 at 20:02
  • I had answered but I guess I didn't understand quite well Commented Nov 23, 2015 at 20:10
  • @Ron.B.I you are right, i have to make sub query (which i never use before). can you give me example please ?? Commented Nov 23, 2015 at 20:15

2 Answers 2

Reset to default
1

Clearing things up, my answer from the comments explained:

While using left join operation (A left join B) - a record will be created for every matching B record to an A record, also - a record will be created for any A record that has no matching B record, using null values wherever needed to complement the fields from B.

I would advise reading up on Using Joins in SQL when approaching such problems.

Below are 2 possible solutions, using different assumptions.

Solution A

Without any assumptions regarding primary key:

We have to sum up the item quantity column to determine the total quantity, resulting in two sums that need to be performed, I would advise using a sub query for readability and simplicity.

select item_tbl.item_id, Sum(item_tbl.qty) as [item_qty], -isnull(Sum(invoice_grouped.qty),0) as [invoice_qty]
from item_tbl left join 
   (select invoice.item_id as item_id, Sum(invoice.qty) as qty from invoice group by item_id) invoice_grouped
on (invoice_grouped.item_id = item_tbl.item_id)
group by item_tbl.item_id

Execution Plan 1

Solution B

Assuming item_id is primary key for item_tbl:

Now we know we can rely on the fact that there is only one quantity for each item_id, so we can do without the sub query by selecting any (max) of the item quantities in the join result, resulting in a quicker execution plan.

select item_tbl.item_id, Max(item_tbl.qty) as [item_qty], -isnull(Sum(invoice.qty),0) as [invoice_qty]
from item_tbl left join invoice on (invoice.item_id = item_tbl.item_id)
group by item_tbl.item_id

Execution Plan 2

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

Comments

1

If your database design is following the common rules, item_tbl.item_id must be unique.

So just change your query:

Select item_tbl.item_id, item_tbl.qty as [item_qty], 
-isnull(Sum(invoice.qty),0) as [invoice_qty] 
from item_tbl 
left join invoice on item_tbl.item_id = invoice invoice.item_id group by item_tbl.item_id, item_tbl.qty
Improve this answer

7 Comments

This actually only works if item_tbl.qty is unique (which I think is true for OP).
As the id is unique, there will only be one qty for each id. So id+qty will also be unique.
Yeah, I kinda misworded that. Since what OP provided is potentially a fragment of the real DB (to simplify the issue), the ID could not be unique (it could be a Foreign Key).
item_id is PK in item_tbl and invoice_id is PK in inovice table. So invoice can have many items of same id, due to which item_tbl copies every time when same item id found more then one time. I guess sub query is best i.e Sum invoice before join. But i dont know how to right sub query :(
@HamzaZaidi no, the issue was simply with item_id inside item_tbl not being unique by itself. Since it is your PK, you shouldn't have an issue with this answer.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.