Search code examples
sqlsql-serverjoincountsum

Joining Two Tables, Getting Sum from multiple part numbers

First, I'm trying to get a sum for the joined tables. I can join the tables and narrow down the part but there are 2 lines. I know this requires an embedded query but scratching my head on putting it together. This joins the two tables to show the quantity for part number "AC011507NANA". What I'm trying to do is there the total qty_ordered which is this case show 1 and 2 but I need the sum of 3 as there are other parts with much larger counts. In addition, there are about 100 part numbers. Is there a way to display insert all the part numbers here or is it only one by one?

SELECT oeordlin_sql.item_no, oeordlin_sql.qty_ordered, oeordlin_sql.ord_no, oeordlin_sql.item_no, oeordhdr_sql.ord_type, oeordhdr_sql.status, oeordhdr_sql.ord_type
FROM oeordlin_sql
INNER JOIN oeordhdr_sql ON oeordlin_sql.ord_no=oeordhdr_sql.ord_no
WHERE oeordhdr_sql.ord_type != 'Q' AND oeordhdr_sql.status != 'L'
AND item_no = 'AC011507NANA';

enter image description here

With David's help I got the proper display. However, I have over 100 part numbers. As mentioned above, is there a way to look up all of them and have the list of items? If not, I'll just go one by one with what I have here.

Item Totals

Solution

  • You have almost all the job done, all you need to implement is a sum() and group by.

    SELECT oeordlin_sql.item_no, sum(oeordlin_sql.qty_ordered) as total
FROM oeordlin_sql
INNER JOIN oeordhdr_sql ON oeordlin_sql.ord_no=oeordhdr_sql.ord_no
WHERE oeordhdr_sql.ord_type != 'Q' AND oeordhdr_sql.status != 'L'
AND item_no = 'AC011507NANA'
GROUP BY oeordlin_sql.item_no;

    This will return the item_no and the total of the qty_ordered. Hope this is what you were looking for