Why cannot I reference the second alias from a subquery containing a UNION?

Question

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

Answer 1

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.