MS SQL Join/Count Query

Question

I have the following tables:

Sessions

Id (int)
UserId (int)
Start (DateTime)
Stop (DateTime)

Users

Id (int)
Username (nvarchar(200))

Logs

SessionId (int)
LogLevelId (int)
Timestamp (DateTime)
Message (varchar(max))

LogLevels

Id (int)
DisplayText (varchar(5))

What I would like is an output that shows an overview of the list of sessions with the following columns:

SessionId | Username | Start | Stop | [total number of logs from each log level]

I have a solution where in C# I:

Select all of the log levels and their associated display text
Get a list of all sessions using the following query:

-

SELECT [Sessions].[Id]
      ,[Username]
      ,[Start]
      ,[Stop]
      ,[Application]
  FROM [Sessions]
  JOIN [Users] ON [Users].[Id] = [UserId]

I loop through each of the results from step 1 to assemble a query to count for each possible log level. Then perform a query per result from step 2 putting a where clause at the end to filter based on specific session. Each of those queries looks something like the following:

-

SELECT 
     COUNT(CASE [Logs].[LogLevelId] WHEN 1 THEN 1 END) AS 'Debugs'
    ,COUNT(CASE [Logs].[LogLevelId] WHEN 2 THEN 1 END) as 'Infos'
    ,COUNT(CASE [Logs].[LogLevelId] WHEN 3 THEN 1 END) as 'Warnings'
    ,COUNT(CASE [Logs].[LogLevelId] WHEN 4 THEN 1 END) as 'Errors'
    ,COUNT(CASE [Logs].[LogLevelId] WHEN 5 THEN 1 END) as 'Fatals'
  FROM [Logs]
  WHERE [SessionId] = |C# SESSION ID HERE|

I know this isn't an optimal solution and I wonder how it would be possible for me to pull all of this information in a single query or in two queries rather than 2 queries + N where N is the total number of session rows.

Parfait · Accepted Answer · 2016-08-01 02:46:10Z

3

Consider joining the former query with latter query all in an aggregate GROUP BY query.

SELECT l.SessionId
       , u.Username
       , s.Start
       , s.Stop
       , COUNT(CASE WHEN l.[LogLevelId] = 1 
                    AND lvl.DisplayText = 'Debugs' THEN 1 END) AS 'Debugs'
       , COUNT(CASE WHEN l.[LogLevelId] = 2 
                    AND lvl.DisplayText = 'Infos' THEN 1 END) as 'Infos'
       , COUNT(CASE WHEN l.[LogLevelId] = 3 
                    AND lvl.DisplayText = 'Warnings' THEN 1 END) as 'Warnings'
       , COUNT(CASE WHEN l.[LogLevelId] = 4 
                    AND lvl.DisplayText = 'Errors' THEN 1 END) as 'Errors'
       , COUNT(CASE WHEN l.[LogLevelId] = 5 
                    AND lvl.DisplayText = 'Fatals' THEN 1 END) as 'Fatals'
FROM     
   [Sessions] s
   JOIN [Users] u ON u.[Id] = s.[UserId]
   JOIN [Logs] l ON l.[SessionId] = s.[Id]
   JOIN [LogLevels] lvl ON lvl.[Id] = l.[LogLevelId]

GROUP BY l.[SessionId]
         , u.Username
         , s.Start
         , s.Stop

edited Aug 1, 2016 at 2:46

answered Jul 31, 2016 at 23:59

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Programming by Permutation Over a year ago

This is perfect! I didn't know you could do that. Is there a way I could also incorporate the Log table's Ids and DisplayNames. Each "COUNT(CASE t.[LogLevelId] WHEN 1 THEN 1 END) AS 'Debugs'" line is really a LogLevel's Id and DisplayName.

Gordon Linoff Over a year ago

The subquery is not necessary.

Parfait Over a year ago

See update, adjust CASE logic as needed. And as @GordonLinoff points out, no need for the derived table, simply join all your tables in an aggregate query.

Collectives™ on Stack Overflow

MS SQL Join/Count Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related