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:

1. Select all of the log levels and their associated display text
2. 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]

3. 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.

Answer 1

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