How to Club Rows in One Row As Others - SQL Server

Mehawitchi
Mehawitchi used Ask the Experts™
on
Hello experts,

The following SQL query yields Results #1 (see below)

SELECT Top 10 ChGroupName AS GROUPNAME, SUM(SCORE) AS SCORE, RANK() OVER(ORDER BY SUM(SCORE) desc) AS RANKING
FROM SmryTable  
GROUP BY ChGroupName
ORDER BY RANKING


I need to alter the above query to make it list top 4 and club entries from 5 onwards as OTHERS, so that the results will look like the following:
Result
Thanks for your help,
Hani
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
;WITH cte1 AS 
(
SELECT Top 10 ChGroupName AS GROUPNAME, SUM(SCORE) AS SCORE, RANK() OVER(ORDER BY SUM(SCORE) desc) AS RANKING 
FROM SmryTable  
GROUP BY ChGroupName 
ORDER BY RANKING
),
cte2 As 
('OTHERS' AS GROUPNAME
, SUM(cte1.SCORE) AS OTHER_SCORE
, 5 NewRank
FROM cte1
WHERE RANKING > 4
)
SELECT GROUPNAME, SCORE, RANKING 
FROM cte1 WHERE RANKING < 5
UNION
SELECT GROUPNAME, OTHER_SCORE, NewRank
FROM ce2;

Open in new window

WITH Data AS (
	SELECT Top 10 
		ChGroupName AS GROUPNAME, SUM(SCORE) AS SCORE, RANK() OVER(ORDER BY SUM(SCORE) desc) AS RANKING
	FROM SmryTable  
	GROUP BY ChGroupName 
)
SELECT 
	CASE WHEN RANKING > 4 THEN 'Other' ELSE GROUPNAME END GROUPNAME,
	SUM(SCORE) AS SCORE,
	MIN(RANKING) RANKING
FROM Data
GROUP BY 
	CASE WHEN RANKING > 4 THEN 'Other' ELSE GROUPNAME END

Open in new window

Author

Commented:
Thanks eghtebas - This looks promising.

I will test and get back to you with feedback.

Best,
Hani
Mike EghtebasDatabase and Application Developer

Commented:
Line 19 has typo. Please change

FROM ce2;

to

FROM cte2;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial