0

Why cannot I use Qty2 as an alias in my query? I receive this error:

Error Code: 1054. Unknown column 'qty2' in 'field list'

SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty FROM ( SELECT 11_counted_stock.part_number as mat, SUM(11_counted_stock.quantity) AS qty FROM 11_counted_stock GROUP BY 11_counted_stock.part_number UNION ALL SELECT 11_stock_actual_whs_costed.material as mat, SUM(11_stock_actual_whs_costed.available_stock) AS qty2 FROM 11_stock_actual_whs_costed GROUP BY 11_stock_actual_whs_costed.material )t WHERE mat = '4532028959' GROUP BY mat
Improve this question
0

1 Answer 1

Reset to default
4

Column names in the UNION query are assigned based on the first SELECT, and you can't have different names for the same (second) column in the result.

My guess would be you want a JOIN, not a UNION.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.