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

Aakriti Choube
Aakriti Choube used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Database Expert
Awarded 2016
Top Expert 2016
Commented:
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

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Question abandoned.
Provided suggestion/solution.

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