CODEWITHSUNDEEP
sqlsql-servert-sql
iAteABug_And_iLiked_it
iAteABug_And_iLiked_it

Reputation: 3795

How to get most common value in a column

I have a table like this in SQL server

UsageTime        Website

10:45:08       yahoo.co.uk

10:24:06       msn.co.uk

09:45:08       lycos.co.uk

I need to GET something like this 

BusiestHour   NoOfWebsitesVisited
10:00         2

I have tried this 

SELECT TOP 3  UsageTime as BusiestHour, COUNT(Website) FROM  NoOfWebsitesVisited
GROUP BY BusiestHour

But this isn't quite right. It doesn't check the whole hour, just a particular value.

Any help will be greatly appreciated :) thanks

Upvotes: 1

Views: 80

Answers (1)

Mithrandir
Mithrandir

Reputation: 25337

SELECT TOP 3   
       DATEPART(HOUR,UsageTime) as BusiestHour, 
       COUNT(Website) FROM  NoOfWebsitesVisited
GROUP BY DATEPART(HOUR,UsageTime)
ORDER BY NoOfWebsitesVisited DESC;

Maybe you want sonething like:

SELECT TOP 3 WITH TIES
       DATEPART(HOUR,UsageTime) as BusiestHour, 
       COUNT(Website) FROM  NoOfWebsitesVisited
GROUP BY DATEPART(HOUR,UsageTime)
ORDER BY NoOfWebsitesVisited DESC;

Upvotes: 5

Related Questions