Here are my tables:

Sales

Products

Categories

Stores

Here is what I tried:

select stores.store_name, categories.product_category  ,count(products.product_code) as total_products
  from sales 

  inner join stores on stores.store_id = sales.store_id
  inner join products on sales.product_code = products.product_code
  inner join categories on products.category_id =categories.ID

  group by categories.product_category , store_name
  order by total_products desc

The result of this query does not give me exactly what I want. I need to get the product_code from every category and every store that is the most sold product. But this query gives me the count of the products, I need product_code.

This is the output I got:

And this is the output I expect:

CodePudding user response：

Join'm. Group'm. Get the biggests.

SELECT 
  store.store_name
, cat.product_category
, product_code
, TotalSales
FROM
(
  SELECT 
    prod.category_id
  , sale.store_id
  , sale.product_code
  , COUNT(sale.ID) AS TotalSales
  , RN = ROW_NUMBER()
         OVER (PARTITION BY sale.store_id, prod.category_id
               ORDER BY COUNT(sale.ID) DESC)
  FROM Products prod
  LEFT JOIN Sales sale
    ON sale.product_code = prod.product_code
  GROUP BY sale.store_id, prod.category_id, sale.product_code
) q
JOIN Categories cat ON cat.ID = q.category_id
JOIN Stores store ON store.store_id = q.store_id
WHERE RN = 1
ORDER BY TotalSales DESC;
GO

store_name | product_category | product_code | TotalSales
:--------- | :--------------- | :----------- | ---------:
First      | Drinks           | 3533         |          2
First      | Pastery          | 6545         |          2
Second     | Drinks           | 4733         |          2
Second     | Pastery          | 5747         |          2
Third      | Drinks           | 5574         |          2
Third      | Pastery          | 5747         |          2

Test on db<>fiddle here