Mysql subtraction with 2 table

Question

Need your help on MySQL. How do i write the query below.

Objective: To summarize each stock items with total incoming, total outgoing & balance.

I've try and cannot work

SELECT
part_db.part_num,
part_db.variant,
SUM(uts_details.qty) AS UTS,
SUM(sir_details.qty) AS SIR,
(UTS - SIR) AS bal
FROM part_db
LEFT OUTER JOIN uts_details ON part_db.part_num = uts_details.part_num
LEFT OUTER JOIN sir_details ON part_db.part_num = sir_details.part_num
GROUP BY part_db.part_num

Output: Unknown column 'UTS' in field list

I've 3 table here,

Part_db (Parts Database)

• Part_num (Primary Key)
• Variant

Uts_details (Incoming)

• Uts_no (Primary Key)
• Part_num
• Qty

Sir_details (Outgoing)

• Sir_no (Primary Key)
• Part_num
• Qty

Output :-

part_num: 12345 variant: ABC total_uts: 400 total_sir: 300 (total_uts - total_sir): 100

The output should show all the parts with and without transaction.

Appreciates your help. Thanks!

Answer 1

You cannot reference a column alias in the same select statement. Here's another option:

SELECT
    part_db.part_num,
    part_db.variant,
    SUM(uts_details.qty) AS UTS,
    SUM(sir_details.qty) AS SIR,
    SUM(uts_details.qty-sir_details.qty) AS bal
FROM part_db
    LEFT OUTER JOIN uts_details ON part_db.part_num = uts_details.part_num
    LEFT OUTER JOIN sir_details ON part_db.part_num = sir_details.part_num
GROUP BY part_db.part_num