Thursday, March 29, 2012

count records in a top 10 query

Hi

Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...

i have this so far, and it dosent give me anything...

col1 is varchar 254

SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)

ex of output

place1 100
place2 50
place3 25
...
place11 500

a query that only gives me the place11 number is enough

thx in advance //MrHere is the number of records that are not in the top 10 list

select count(*) number
from myTable
where col1 not in
(select top 10 col1 from myTable)
group by col1
order by count(*) desc
)

No comments:

Post a Comment