top 25 rows of the highest numbers in a field

Question

Post reply

top 25 rows of the highest numbers in a field

cidr

Ten Centuries

Points: 1353
More actions
August 24, 2009 at 9:36 am

#73313

Hi there,
The database I'm using to write a query records information about highstreet stores. When a store has a problem they phone a company who sends out contractors to fix the problem.
I want to write a query that will list the top 25 stores that have the most calls logged (i.e. have called the most). The query will have the store name and using COUNT, will have a count of the calls logged against each store.
The child table that records the calls made by each store will be included in the query. I can then use COUNT on the storeID and group by store and this will count the amount of times the stores are listed in the query.
I'm unsure how to return the top 25 stores with the highest count. Does anyone know how I may do this.
I help, of course, is appreciated.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

From the description a query with group by and a top 25 would work ordered by count(*)

select top 25 Store_name, count(*)

from CallLog

Group By Store_name

Order by Count(*)

This is just a guess based on the information given, if you would like a more complete solution then post the exact table definitions and some sample data

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

pwatson, the below is one example of how to post readily consumable code, so we can simply cut and paste into QA. This is just a general example of how to do what you are asking with a parent/child table relationship. Now, if the child table contains the store name, rather than a store id, you may not need the parent table, and you said the child table had to be included. Also, you did not indicate the time frame you are interested in. Which is worse, a store that has 30 calls over 6 months, or a store that has only 20 calls, but all within one week?

IF OBJECT_ID('TempDB..#ParentTable','u') IS NOT NULL

DROP TABLE #ParentTable

CREATE TABLE #ParentTable

(

StoreID INT,

StoreName VARCHAR(20)

)

INSERT INTO #ParentTable

SELECT 1,'Store1' UNION ALL

SELECT 2,'Store2' UNION ALL

SELECT 3,'Store3' UNION ALL

SELECT 4,'Store4' UNION ALL

SELECT 5,'Store5'

IF OBJECT_ID('TempDB..#ChildTable','u') IS NOT NULL

DROP TABLE #ChildTable

CREATE TABLE #ChildTable

(

StoreID INT,

CallTime DATETIME

)

INSERT INTO #ChildTable

SELECT 1,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE()) UNION ALL

SELECT 2,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE()) UNION ALL

SELECT 3,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE()) UNION ALL

SELECT 4,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE()) UNION ALL

SELECT 5,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE()) UNION ALL

SELECT 5,DATEADD(MINUTE,ABS(CHECKSUM(NEWID()))/100000,GETDATE())

SELECT TOP 2

p.StoreName,

t1.CallCount

FROM #ParentTable p,

(

SELECT

StoreID,

CallCount = COUNT(*)

FROM #ChildTable

GROUP BY StoreID

) t1

WHERE p.StoreID = t1.StoreID

ORDER BY t1.CallCount DESC

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.