adding each row value from different table

Question

Problem: 3 tables: tbl_product, tbl_soldproducts, tbl_purchasedetail tbl_product has a primary key of prod_no which is foreign key on each of the 2 table. tbl_products tbl_purchasedetail tbl_soldproducts

In tbl_soldproducts I have column item_sold describe how many items sold for this product per transaction. In tbl_purchasedetail I have qty_purchase which describe as number of item purchase per transaction. I need to count the total item sold and purchase per product. Ex output:

Prod no | item_sold |qty_purchase | left
   1    |    23     |     25      |  2
   2    |    1      |     10      |  9

My current code which display wrong output:

   SELECT TP.prod_no,TP.barcode,TP.prod_name, COUNT(TS.qty) as num_sold,
   COUNT(TPS.qty_delivered) as num_delivered
   FROM tbl_product AS TP 
   LEFT JOIN tbl_soldproducts AS TS
   on (TS.prod_no = TP.prod_no)
   LEFT JOIN tbl_purchasedetail AS TPS
   on (TPS.prod_no = TP.prod_no)
   group by TS.prod_no
   ORDER BY TP.prod_name

Tim Biegeleisen · Accepted Answer · 2017-01-10 04:05:19Z

1

You never showed us any sample data, but I think you should be aggregating the two auxiliary tables in separate subqueries, and then joining those results back to tbl_product. Something like this should work:

SELECT TP.prod_no,
       TP.barcode,
       TP.prod_name,
       COALESCE(TS.num_sold, 0) AS num_sold,
       COALESCE(TPS.num_delivered, 0) AS num_delivered
FROM tbl_product TP
LEFT JOIN
(
    SELECT prod_no, COUNT(*) AS num_sold
    FROM tbl_soldproducts
    GROUP BY prod_no
) TS
    ON TP.prod_no = TS.prod_no
LEFT JOIN
(
    SELECT prod_no, COUNT(*) AS num_delivered
    FROM tbl_purchasedetail
    GROUP BY prod_no
) TPS
    ON TP.prod_no = TPS.prod_no
ORDER BY TP.prod_name

Your original query might have been OK if all you wanted to select from tbl_product was the product number. But your query could have strange behavior as is, because you are selecting non aggregate columns from tbl_product while using GROUP BY. You should go with an approach such as what I suggested instead if you want to also select other columns.

answered Jan 10, 2017 at 4:05

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

propaganja Over a year ago

Thank you. I got it running by changing COUNT to SUM. Now it is adding all the quantity sold and quantity purchase.

Jiang · Accepted Answer · 2017-01-10 04:09:06Z

0

You'd better give us a database case. @Tim Biegeleisen That's right

answered Jan 10, 2017 at 4:09

Jiang

517 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

Hi Jiang, you should leave this as a comment, and if you can't then maybe get some more rep so you can comment.

Jiang Over a year ago

Sorry, I do not have enough reputation for append comments, I just used it shortly

Collectives™ on Stack Overflow

adding each row value from different table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related