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.

  • 1
    Don't you want SUM() instead of COUNT()? – Rick James Jan 5 '18 at 14:15
  • I have done with Sum and Count but both getting same results. – Steven Smith Jan 5 '18 at 14:29
  • 1
    I hope you realize that GROUP BY and ORDER BY clauses apply only to the second SELECT. – mustaccio Jan 5 '18 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. – Steven Smith Jan 5 '18 at 16:21
  • 1
    SUM will get a larger quantity if qty is bigger than 1. – Rick James Jan 5 '18 at 17:46
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;
  • 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? – Steven Smith Jan 23 '18 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 '18 at 10:36

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