-- Distributors and duplicate SKUs
-- This report shows the number of products per distributor
-- FILTER: {
--   filter: "link",
--   column: "link",
--   "params": {
--         "blank": true,
--         "display": "View Products"
--     }
-- }

SELECT (SELECT name from simplebrsprod.business_location bl WHERE bl.id = A.location_id) as distributor,
       a.name, a.vendor_item_reference, a.pack, a.size, a.is_broken_case
FROM simplebrsprod.deal_product a
         JOIN (SELECT name, vendor_item_reference, pack, size, location_id, COUNT(*)
               FROM simplebrsprod.deal_product
               GROUP BY name, vendor_item_reference, pack, size, location_id
               HAVING count(*) > 1 ) b
              ON a.vendor_item_reference = b.vendor_item_reference
                  AND a.location_id = b.location_id
ORDER BY a.location_id, a.vendor_item_reference;