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):

Capture.JPG
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
Aakriti ChoubeAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided suggestion/solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.