How to count number of duplicated items in my from relational table

Question

I have order and a product tables. The order table looks like this:

uid  id  ref     order_id
---  --  ------  --------------
1    3   abc.    112
2    3   def     124

And the product table looke liks this

uid  id  sku     order_id
---  --  ------  --------------
1    6   rs-123  112
2    7   rs-123  112
2    8   rs-abc  124

So I need a query where I get all the orders that have more than one identical sku like so:

order_id        sku          qty
--------        ---------    --------
112             rs-123       2

There could be orders with 2, 3 or more items with same sku. I do not want show any order that does not have duplicated skus

I've tried this:

SELECT sku, order_id,
COUNT(distinct sku) As Total
FROM products
GROUP BY order_id
HAVING (COUNT(distinct sku) > 1)

But it's not giving the expected results. Any ideas?

Answer 1

You are almost there. Just add sku to the GROUP BY clause, and remove distinct from the counts - you might as well use COUNT(*):

SELECT sku, order_id, COUNT(*) As Total
FROM products
GROUP BY sku, order_id
HAVING COUNT(*) > 1