How to Get "top N" and "Other Than Top N" values in SQL and SSRS?

Hi,

I have some data that includes Broker names, product they bought, and corresponding values and quantity of the products. I need to show top 4 brokers (each product wise) based on values of the product as well as "other than top 4". If for a particular product, there is only two brokers that have some value and rest of the brokers have just quantities and value equal to 0, then i want their sum to be removed from "top 4" field and added into the "other than top 4" column. I know it sounds really complicated. I am preparing this report on sql and SSRS. Below is the sample of desired report:

Sample Data Table:

product      Brokername      Values      quantity
Casualty      ABC        500        2
Casualty        XYZ        300        3
Casualty        LMN        250        4
Casualty        PQR        0        4
Casualty      OPQ        0        6
marine        XYZ        200        1
marine        ABC        300        2
energy        LMN        600        4
energy        MNQ        0        1

Desired report (On SSRS):

I have inserted the image of the desired report. I am almost done with rest of the data on SSRS. These are some of my queries.

Thanks

You can do like below - replicate the same for 2nd half.

SELECT [Broker Category] , [Values] , [quantity] 
FROM 
(
	SELECT 'Top 4' [Broker Category] , SUM( CASE WHEN rnk < 5 AND [Values] <> 0  THEN [Values] ELSE 0 END ) [Values]
									 , SUM( CASE WHEN rnk < 5 AND [Values] <> 0  THEN quantity ELSE 0 END ) [quantity]
	FROM 
	(																
		SELECT [Values] , quantity , ROW_NUMBER() OVER (PARTITION BY product ORDER BY [Values] DESC) rnk FROM brokers
		WHERE product = 'Casualty' 
	)u
	UNION ALL
	SELECT 'Other than Top 4' [Broker Category] , SUM( CASE WHEN rnk > 4 OR [Values] = 0 THEN [Values] ELSE 0 END ) [Values]
												, SUM( CASE WHEN rnk > 4 OR [Values] = 0 THEN quantity ELSE 0 END ) [quantity]
	FROM 
	(																
		SELECT [Values] ,quantity , ROW_NUMBER() OVER (PARTITION BY product ORDER BY [Values] DESC) rnk FROM brokers
		WHERE product = 'Casualty' 
	)u
)r
UNION ALL
SELECT [Broker Category] , SUM([Values]) , SUM([quantity])
FROM 
(
	SELECT 'Casualty Total' [Broker Category] , SUM( CASE WHEN rnk < 5 AND [Values] <> 0  THEN [Values] ELSE 0 END ) [Values]
									 , SUM( CASE WHEN rnk < 5 AND [Values] <> 0  THEN quantity ELSE 0 END ) [quantity]
	FROM 
	(																
		SELECT [Values] , quantity , ROW_NUMBER() OVER (PARTITION BY product ORDER BY [Values] DESC) rnk FROM brokers
		WHERE product = 'Casualty' 
	)u
	UNION ALL
	SELECT 'Casualty Total' [Broker Category] , SUM( CASE WHEN rnk > 4 OR [Values] = 0 THEN [Values] ELSE 0 END ) [Values]
												, SUM( CASE WHEN rnk > 4 OR [Values] = 0 THEN quantity ELSE 0 END ) [quantity]
	FROM 
	(																
		SELECT [Values] ,quantity , ROW_NUMBER() OVER (PARTITION BY product ORDER BY [Values] DESC) rnk FROM brokers
		WHERE product = 'Casualty' 
	)u
)o GROUP BY [Broker Category]

Open in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
Broker Category  Values      quantity
---------------- ----------- -----------
Top 4            1050        9
Other than Top 4 0           10
Casualty Total   1050        19

(3 row(s) affected)

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 38

Pawan KumarDatabase ExpertCommented: 2017-11-10

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Pawan KumarDatabase ExpertCommented: 2017-11-29

Question abandoned.
Provided suggestion/solution.

How to Get "top N" and "Other Than Top N" values in SQL and SSRS?

Who is Participating?