Query does not filter duplicate _sku numbers

Question

I need to find ALL the post_id in table wp_postmeta that has duplicate sku numbers. I did this with following sql query but the query is not complete. Now I get the full list of all products as result. The field post_id is missing but I do not know how to set filter on it if count(post_id > 1) Is there someone of you who can modify the query and add the missing part to filter only the duplicate sku? Would be very helpful for me. Thanks in advance.

SELECT * FROM `wp_postmeta`
WHERE `meta_key` LIKE '%sku%' 

Answer 1

I haven't tested this, but this should give you the count of skus for each post_id:

SELECT post_id, COUNT(`post_id`) AS sku_count FROM `wp_postmeta`
WHERE `meta_key` LIKE '%sku%'
GROUP BY `post_id`

To have it return only rows where the count is greater than one is trickier. Off the top of my head I'd probably use the above as a subquery:

SELECT * from (< insert the above query >) AS counts where counts.sku_count > 1

Might need some tweaking.

If you can avoid using LIKE, or at least get rid to one of the % wildcards, you will improve performance