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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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:
Question abandoned.
Provided suggestion/solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.