record count top 5 then other

Question

record count top 5 then other

SQL_NuB

Hall of Fame

Points: 3666
More actions
August 10, 2012 at 12:46 pm

#262554

I have a requirement to show and count the top 5 records and then they want a count of the remaining records and show them as 'other'
How can I count the remaining records that are outside of the top 5?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

Top 5 by what criteria? If you show the structure of your table and what you are basing the top 5 off of, we could easily help you find a solution here.

SQL_NuB Hall of Fame Points: 3666 More actions · Answer 2

my query looks something like this (I can't show the actual ddl or query)

select top 5 states, customers from tbl1

where type = 'Rented' and (rentalperiod between 200101 and 200012)

I want to the top 5 states with rental customers, and then count the remaining states with rental customers that meet the requirements

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Have to agree here, not enough information to even give a good shot in the dark.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

SQL_NuB (8/10/2012)
my query looks something like this (I can't show the actual ddl or query)
select top 5 states, customers from tbl1
where type = 'Rented' and (rentalperiod between 200101 and 200012)
I want to the top 5 states with rental customers, and then count the remaining states with rental customers that meet the requirements

Still too vague. Can you show use what the results of the query should look like? It would also help if you could provide us with the DDL (CREATE TABLE statement) for the table(s), and some sample data for the table9s) as well.

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 5

That and the top 5 of your results are not necessarily guarenteed if you don't have a order by statement in your query although I'm not sure if that is a big issue in your case.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Just guessing...

;WITH Top5 AS(

SELECT TOP 5

state,

COUNT( DISTINCT customer) AS customers

FROM Tbl1

where type = 'Rented' and (rentalperiod between 200101 and 200012)

GROUP BY state

ORDER BY 2 DESC)

SELECT ISNULL( Top5.state, 'Other') AS state,

COUNT( DISTINCT Tbl1.customer) AS customers

FROM Tbl1

LEFT JOIN Top5 ON Tbl1.state = Top5.state

where Tbl1.type = 'Rented'

and (Tbl1.rentalperiod between 200101 and 200012)

GROUP BY ISNULL( Top5.state, 'Other')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

SQL_NuB (8/10/2012)
my query looks something like this (I can't show the actual ddl or query)

But you could change the names of the table and columns and post some DDL and sample data (doesn't have to be actual data either).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 8

I also just noticed your between criteria in your original query you posted won't return you any results since the first number in your between is greater than the second number.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Luis Cazares (8/10/2012)
SQL_NuB (8/10/2012)
my query looks something like this (I can't show the actual ddl or query)
But you could change the names of the table and columns and post some DDL and sample data (doesn't have to be actual data either).

Luis is quite correct. It is really hard to provide any kind of solid answer if you can't give us enough to understand what it is you are trying to accomplish.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

just an idea ...anywhere close to what you require?

CREATE TABLE [dbo].[Table_1](

[ID] [int] NULL,

[Val] [varchar](1) NULL

) ON [PRIMARY]

INSERT INTO [dbo].[Table_1]([ID], [Val])

SELECT 1, N'A' UNION ALL

SELECT 2, N'B' UNION ALL

SELECT 3, N'C' UNION ALL

SELECT 4, N'A' UNION ALL

SELECT 5, N'B' UNION ALL

SELECT 6, N'C' UNION ALL

SELECT 7, N'A' UNION ALL

SELECT 8, N'B' UNION ALL

SELECT 9, N'C' UNION ALL

SELECT 10, N'A' UNION ALL

SELECT 11, N'A' UNION ALL

SELECT 12, N'B' UNION ALL

SELECT 13, N'C' UNION ALL

SELECT 14, N'A' UNION ALL

SELECT 15, N'B' UNION ALL

SELECT 16, N'C' UNION ALL

SELECT 17, N'A' UNION ALL

SELECT 18, N'D' UNION ALL

SELECT 19, N'E' UNION ALL

SELECT 20, N'F' UNION ALL

SELECT 21, N'G' UNION ALL

SELECT 22, N'H' UNION ALL

SELECT 23, N'C' UNION ALL

SELECT 24, N'A' UNION ALL

SELECT 25, N'A' UNION ALL

SELECT 26, N'B' UNION ALL

SELECT 27, N'C' UNION ALL

SELECT 28, N'A' UNION ALL

SELECT 29, N'B'

;with CTE as (

SELECT Val, COUNT(ID) AS CountID, ROW_NUMBER () over ( order by COUNT(ID) desc) as rn

FROM Table_1

GROUP BY Val

)

SELECT Val, CountID as Cnt

FROM cte

WHERE (rn < 4)

UNION ALL

SELECT 'Others' AS Val, SUM(CountID) AS Cnt

FROM cte

WHERE (rn >= 4)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day