Calculate mode (or frequency) distribution of a value over time in SQL Server

Question

Given the following table, how does one calculate the hourly mode, or value with the highest frequency by hour?

CREATE TABLE Values ( ValueID int NOT NULL, Value int NOT NULL, LogTime datetime NOT NULL )

So far, I've come up with the following query.

SELECT count(*) AS Frequency, DatePart(yy, LogTime) as [Year], DatePart(mm, LogTime) as [Month], DatePart(dd, LogTime) as [Day], DatePart(hh, LogTime) as [Hour] FROM Values GROUP BY Value, DatePart(yy, LogTime), DatePart(mm, LogTime), DatePart(dd, LogTime), DatePart(hh, LogTime)

However, this yields the frequency of each distinct value by hour. How do I add a constraint to only return the value with the maximum frequency by hour?

Thanks

Answer 1

Nest the aggregates...

SELECT
    MAX(Frequency) AS [Mode],
    [Year],[Month],[Day],[Hour]
FROM
    (SELECT
         COUNT(*) AS Frequency, 
         DatePart(yy, LogTime) as [Year], 
         DatePart(mm, LogTime) as [Month], 
         DatePart(dd, LogTime) as [Day], 
         DatePart(hh, LogTime) as [Hour]
    FROM 
         Values 
    GROUP BY 
         Value, 
         DatePart(yy, LogTime), 
         DatePart(mm, LogTime), 
         DatePart(dd, LogTime), 
         DatePart(hh, LogTime)
    ) foo
GROUP By
    [Year],[Month],[Day],[Hour]

Answer 2

The following query may look odd... but it works and it gives you what you want. This query will give you the value that had the highest frequency in a particular "hour" (slice of time).

I am NOT dividing into Year, Month, Day, etc... only hour (as you requested) even though you had those other fields in your example query.

I chose to do "MAX(Value)" below, because the case can come up where more than one "value" tied for first place with the highest frequency by hour. You can choose to do MIN, or MAX or some other 'tiebreaker' if you want.

WITH GroupedValues (Value, Frequency, Hour) AS
    (SELECT
     Value,
     COUNT(*) AS Frequency,
     DATEPART(hh, LogTime) AS Hour
    FROM
     dbo.MyValues
    GROUP BY
     Value,
     DATEPART(hh, LogTime))

SELECT
    MAX(Value) AS Value,
    a.Hour
FROM
    GroupedValues a INNER JOIN
     (SELECT MAX(Frequency) AS MaxFrequency,
      Hour FROM GroupedValues GROUP BY Hour) b
    ON a.Frequency = b.MaxFrequency AND a.Hour = b.Hour
GROUP BY
    a.Hour