0

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_products tbl_purchasedetail tbl_purchasedetail tbl_soldproducts 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
Improve this question

2 Answers 2

Reset to default
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.

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

1 Comment

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

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

Improve this answer

2 Comments

Hi Jiang, you should leave this as a comment, and if you can't then maybe get some more rep so you can comment.
Sorry, I do not have enough reputation for append comments, I just used it shortly

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.