I am trying to find the 3 top selling products in each category. But the SQL I tried does not yield the correct results. Here's the things I did:
Products Table:
CREATE TABLE [dbo].[Products](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](100) NOT NULL,
[category] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[id] ASC
)
id | name | category |
---|---|---|
1 | prod1 | cat1 |
2 | prod2 | cat1 |
3 | prod3 | cat1 |
4 | prod4 | cat1 |
5 | prod5 | cat1 |
6 | prod6 | cat2 |
7 | prod7 | cat2 |
8 | prod8 | cat2 |
10 | prod10 | cat2 |
OrderItems table:
CREATE TABLE [dbo].[OrderItems](
[id] [int] IDENTITY(1,1) NOT NULL,
[prodID] [int] NOT NULL,
[quantity] [int] NOT NULL,
CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED
(
[id] ASC
)
ALTER TABLE [dbo].[OrderItems] WITH CHECK ADD CONSTRAINT [FK_OrderItems_Products] FOREIGN KEY([prodID])
REFERENCES [dbo].[Products] ([id])
ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
id | prodID | quantity |
---|---|---|
1 | 1 | 10 |
2 | 2 | 6 |
3 | 2 | 6 |
4 | 4 | 3 |
5 | 3 | 2 |
6 | 6 | 10 |
7 | 7 | 1 |
SQL I tried:
select *
from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p2
where p2.sales >= (
select distinct(sales)
from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p3
where p2.category = p3.category
order by sales desc
offset 2 rows fetch next 1 rows only)
Expected Result:
id | category | sales |
---|---|---|
1 | cat1 | 10 |
2 | cat1 | 12 |
4 | cat1 | 3 |
6 | cat2 | 10 |
7 | cat2 | 1 |
Actual Result:
id | category | sales |
---|---|---|
1 | cat1 | 10 |
2 | cat1 | 12 |
4 | cat1 | 3 |
What is the mistake in the query? And is there a better way to achieve the result?
CodePudding user response:
You can join the tables and aggregate to get the total sales for each product.
Also use ROW_NUMBER()
window function based on the category of the product and ordered by the total sales to rank each product and filter:
SELECT id, category, sales
FROM (
SELECT p.id,
MAX(category) category,
SUM(o.quantity) sales,
ROW_NUMBER() OVER (PARTITION BY MAX(category) ORDER BY SUM(o.quantity) DESC) rn
FROM Products p INNER JOIN OrderItems o
ON o.prodID = p.id
GROUP BY p.id
) t
WHERE rn <= 3
ORDER BY id;
Or, aggregate first in OrderItems
and then join:
SELECT id, category, sales
FROM (
SELECT p.id,
p.category,
o.sales,
ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.sales DESC) rn
FROM Products p
INNER JOIN (
SELECT prodID, SUM(quantity) sales
FROM OrderItems
GROUP BY prodID
) o ON o.prodID = p.id
) t
WHERE rn <= 3
ORDER BY id;
See the demo.