Two tables, same Attributes unable to count, I am working on Two table together for count how many SKU.

As I'm able to sum them up in how much amount with no problem. but I need a count on how many QTY in SKU.

I am able to get a count in SKU in one table only but unable to do two table. I have tried UNION SELECT but nothing working

SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'   

UNION SELECT
     transaction_type, sku, count(quantity_purchased) as SKU_ORDER

FROM
    settlements_qty_test1
WHERE
        transaction_type = 'order'
        AND amount_description = 'Principal'
        AND amount_type = 'ItemPrice'   

GROUP BY sku 
ORDER BY sku 


I have used this similar method for how much, and seems it working fine but not this one. Seems I am missing something?

I am still rookie MySQL.
You could try to fetch all affected records of both tables using UNION ALL and then COUNT the records. (I'm not sure if you want to COUNT records or to SUM quantities.)

Notice you should add transaction_type to the GROUP BY clause.

If you want you can move the WHERE clause to the main query.

SELECT transaction_type, sku, COUNT(quantity_purchased) as SKU_ORDER
FROM   (
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'   
        UNION ALL
        SELECT transaction_type, sku, quantity_purchased
        FROM   settlements_qty_test1
        WHERE  transaction_type = 'order'
        AND    amount_description = 'Principal'
        AND    amount_type = 'ItemPrice'   
       ) t1
GROUP BY transaction_type, sku
ORDER BY sku;