October 19, 2009 at 10:10 am
Hello,
I have a query:select count(1) as Total, Category from manager group by Category order by Total Desc, the result looks like:
Total Category
84Operations
12Toronto
9Retail
7SharedServices
6Markham
5Ontario
4CanWest
3Finance
3GlobalTransactionBanking
2HumanResources
2ElectronicBanking
2ExecutiveOfficeAdmin
1GlobalRiskManagement
1Legal
1Audit
1ScotiaInsurance
1ScotiaLeasing
1Quebec
1RealEstate
Now what I am looking for is to put all the categories with Total under say 3 in one single group, so the result looks like:
Total Category
84Operations
12Toronto
9Retail
7SharedServices
6Markham
5Ontario
4CanWest
19 Others
Can any one tell me how to do this? Thanks in advance.
October 19, 2009 at 10:25 am
There might be a better way, but I'd actually use a HAVING clause and a union.
select count(1) as Total, Category
from manager
group by Category
having count(1) > 3
order by Total Desc
union
(
select count(1) as Total, Category
from manager
group by Category
having count(1) < 3
order by Total Desc
)
I've left it out, but you need to take the second query and re-select from it and total up all the values.
IF ordering matters, add another field to each query, say a static 1 and 2, and then order by that.
October 19, 2009 at 10:34 am
Yes, same as Steve (with some test data and summing)
DRop table Manager
CREATE TABLE MANAGER(id int, category varchar(50))
INSERT INTO MANAGER VALUES(1,'Operations')
INSERT INTO MANAGER VALUES(1,'Operations')
INSERT INTO MANAGER VALUES(1,'Operations')
INSERT INTO MANAGER VALUES(1,'Operations')
INSERT INTO MANAGER VALUES(1,'Toronto')
INSERT INTO MANAGER VALUES(1,'Toronto')
INSERT INTO MANAGER VALUES(1,'Toronto')
INSERT INTO MANAGER VALUES(1,'Toronto')
INSERT INTO MANAGER VALUES(1,'Toronto')
INSERT INTO MANAGER VALUES(1,'RealEstate')
INSERT INTO MANAGER VALUES(1,'Quebec')
INSERT INTO MANAGER VALUES(1,'Quebec')
INSERT INTO MANAGER VALUES(1,'ScotiaLeasing')
INSERT INTO MANAGER VALUES(1,'ScotiaLeasing')
INSERT INTO MANAGER VALUES(1,'ScotiaLeasing')
Select count(1) as total, category from Manager
Group by category
Having count(*) > 3
UNION
Select SUM(total), 'Others'
From (Select count(1) as total, category from Manager
Group by category
Having count(*) <= 3) test
---------------------------------------------------------------------------------
October 19, 2009 at 10:38 am
Thank you guys.
October 20, 2009 at 6:21 am
just another method..
select sum(Total),case when RowNum<4 then Category else 'Others' end from (
Select count(1) as Total,row_number() over (order by count(1) desc) as RowNum,
Category from manager group by Category) a
group by case when RowNum<4 then RowNum else 0 end,case when RowNum<4 then Category else 'Others' end
order by sum(Total) desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply