Hello All,
Please help me on this
I have a table where I have columns like Team,Category and Sales. my requirement is I need to get top 10 categories based on sales for each Team.
below is the query I have written and getting only T1 teams top 10 categories based on sales
But I need to get both T1 and T2 teams data with categories based on sales
Below is the code I tried so far.
select top 10 sum(sales),Teams,Category from Sales
group by Teams,Category
order by sum(sales) desc
I am getting only Team t1 top 10 sales
December 18, 2019 at 10:01 am
An interesting problem, because you need to group/partition by team and categories to get each team's sales figures per category, and then just by team to get the top categories per team. You'll have a much better chance of someone helping you on this if you provide table DDL in the form of a CREATE TABLE statement and sample data in the form of INSERT statements.
John
December 18, 2019 at 3:21 pm
WITH sales_by_team_and_category AS
(
SELECT
SUM(sales) AS Total_Sales
,Teams
,Category
,ROW_NUMBER() OVER(PARTITION BY Teams, Category ORDER BY SUM(sales) DESC) AS rn
FROM Sales
GROUP BY Teams, Category
)
SELECT *
FROM sales_by_team_and_category
WHERE rn <= 10
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 18, 2019 at 4:07 pm
An alternative solution
;WITH cte AS
(
SELECT s.Teams, s.Category, SUM(s.Sales) Sales
FROM dbo.Sales s
GROUP BY s.Teams, s.Category
),
cte2 as
(
SELECT DISTINCT Teams
FROM cte
)
SELECT cte2.Teams, x.Category, x.Sales
FROM cte2
CROSS APPLY(SELECT TOP(10)
cte.Category,
cte.Sales
FROM cte
WHERE cte.Teams = cte2.Teams
ORDER BY cte.Sales DESC) x
December 18, 2019 at 6:27 pm
An alternative solution
;WITH cte AS
(
SELECT s.Teams, s.Category, SUM(s.Sales) Sales
FROM dbo.Sales s
GROUP BY s.Teams, s.Category
),
cte2 as
(
SELECT DISTINCT Teams, Category
FROM cte
)
SELECT Teams, Category, x.Sales
FROM cte2
CROSS APPLY(SELECT TOP(10) Sales
FROM cte
WHERE cte.Teams = cte2.Teams
AND cte.Category = cte2.Category
ORDER BY cte.Sales DESC) x
Without sample data to test, it's hard to make comparisons, but this is almost definitely going to perform WORSE than my solution, because it requires at least two scans of the table, whereas mine should only require one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 18, 2019 at 7:45 pm
Jonathan AC Roberts wrote:An alternative solution
;WITH cte AS
(
SELECT s.Teams, s.Category, SUM(s.Sales) Sales
FROM dbo.Sales s
GROUP BY s.Teams, s.Category
),
cte2 as
(
SELECT DISTINCT Teams
FROM cte
)
SELECT cte2.Teams, x.Category, x.Sales
FROM cte2
CROSS APPLY(SELECT TOP(10)
cte.Category,
cte.Sales
FROM cte
WHERE cte.Teams = cte2.Teams
ORDER BY cte.Sales DESC) xWithout sample data to test, it's hard to make comparisons, but this is almost definitely going to perform WORSE than my solution, because it requires at least two scans of the table, whereas mine should only require one.
Drew
Yes I agree, it is going to perform worse.
December 18, 2019 at 9:26 pm
drew.allen wrote:Jonathan AC Roberts wrote:An alternative solution
;WITH cte AS
(
SELECT s.Teams, s.Category, SUM(s.Sales) Sales
FROM dbo.Sales s
GROUP BY s.Teams, s.Category
),
cte2 as
(
SELECT DISTINCT Teams
FROM cte
)
SELECT cte2.Teams, x.Category, x.Sales
FROM cte2
CROSS APPLY(SELECT TOP(10)
cte.Category,
cte.Sales
FROM cte
WHERE cte.Teams = cte2.Teams
ORDER BY cte.Sales DESC) xWithout sample data to test, it's hard to make comparisons, but this is almost definitely going to perform WORSE than my solution, because it requires at least two scans of the table, whereas mine should only require one.
Drew
Yes I agree, it is going to perform worse.
Wouldn't it be a hoot if the optimizer fooled everyone? That IS the nature of CTEs. No one will actually know until it's tested. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Jonathan AC Roberts wrote:Yes I agree, it is going to perform worse.
Wouldn't it be a hoot if the optimizer fooled everyone? That IS the nature of CTEs. No one will actually know until it's tested. 😉
CREATE TABLE dbo.Sales
(
Teams varchar(20) not null,
Category varchar(20),
Sales real
);
go
insert into Sales(Teams,Category,Sales)
SELECT CONCAT('Team',t1.N) Teams,
CONCAT('Category',t0.N) Category,
CAST(RAND(CHECKSUM(NEWID()))*10.8 as real) +95 Sales
FROM dbo.Tally a
CROSS APPLY(SELECT TOP(10) N FROM Tally t) t0
CROSS APPLY(SELECT TOP(100) N FROM Tally t) t1
WHERE a.N<10000
CREATE INDEX IX_Sales_1 ON Sales(Teams,Category,Sales DESC)
go
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply