0

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.

6
  • 1
    Don't you want SUM() instead of COUNT()?
    – Rick James
    Jan 5, 2018 at 14:15
  • I have done with Sum and Count but both getting same results. Jan 5, 2018 at 14:29
  • 1
    I hope you realize that GROUP BY and ORDER BY clauses apply only to the second SELECT.
    – mustaccio
    Jan 5, 2018 at 15:59
  • Yes, you are right as I just aware of it a half hour before you mention it, I was reading on MySQL document. Jan 5, 2018 at 16:21
  • 1
    SUM will get a larger quantity if qty is bigger than 1.
    – Rick James
    Jan 5, 2018 at 17:46

1 Answer 1

1

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;
2
  • That is perfect, If I want another add new column, like Transaction type='refund', do I write it on From Section () t1 or new section as ()t2? Jan 23, 2018 at 10:33
  • You should add it in both UNION selects, in the outer select and in GROUP BY section. If have any problem add a new question and post the link as a comment in this answer.
    – McNets
    Jan 23, 2018 at 10:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.