CODEWITHSUNDEEP
sql-servert-sql
Jonathan
Jonathan

Reputation: 321

T-SQL Select distinct id's in logging database and count entries in past one hour

I have an exception logger table in a SQL Server database with three columns, in which multiple applications log .NET exceptions.

I need to query that will count how many times an application has logged an exception to this database in the past hour. I also need all applications to appear in the list, even if there has been no exception logged in the past hour.

The table basically looks like this:

ID, AppName, TimeStamp

I've tried creating temp tables with just the distinct list of AppName, and then fumbling around with counts and sum and group by timestamp functions, but have had no results that seems to get me close to what I need.

Any help appreciated!

Upvotes: 1

Views: 67

Answers (1)

Rob Farley
Rob Farley

Reputation: 15849

SELECT a.AppName, COUNT(l.ID) as NumLogs
FROM (SELECT DISTINCT AppName FROM dbo.Logger) AS a
LEFT JOIN dbo.Logger AS l
  ON l.AppName = a.AppName 
  AND l.TimeStamp > DATEADD(hour, -1, SYSDATETIME())
GROUP BY a.AppName;

It's important to have the time filter in the ON clause here, to let the outer join has its proper effect.

Upvotes: 2

Related Questions