Click here to Skip to main content
15,879,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good day

I need to group by user,Subcategory then sum total category counts per user and then order by sum category count per user desc get top 10 users based on sum total category counts per user



I am having trouble get the 10 users with most sum total category counts per user

What I have tried:

SELECT user,Subcategory,count(orderID) "Count" FROM Order group by user ,Subcategory with Rollup ORDER BY 1
Posted
Updated 26-Sep-16 12:25pm

1 solution

I made my own schema based on your query as I don't have any sample data but see if this works.

What i did is created crap data for 12 users and you'll notice that this query should exclude user2 and user12 i believe.

If this isn't what you are looking for, use improve question link in your question to update it with sample schema and a few example data items.

SQL
--SELECT user,Subcategory,count(orderID) "Count" FROM Order group by user ,Subcategory with Rollup ORDER BY 1 



DECLARE @Order TABLE 
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Username varchar(25) NULL,
	SubCategory VARCHAR(50) NULL,
	OrderId INT NULL
);


INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user1','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user2','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user3','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user4','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user5','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user6','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user7','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user8','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user9','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user10','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user11','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user12','1',1)
INSERT INTO @Order (Username, SubCategory, OrderId) VALUES ('user12','1',1)


--Get top 10 by subcategory
SELECT 
	TOP 10
	Username,
	COUNT(*)
FROM @Order
GROUP BY Username, SubCategory
ORDER BY COUNT(*) DESC


--Get top 10 by subcategory and orderid
SELECT 
	TOP 10
	Username,
	COUNT(*)
FROM @Order
GROUP BY Username, SubCategory, OrderId
ORDER BY COUNT(*) DESC
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900