Dex Dex - 4 months ago 23
SQL Question

MS SQL Join/Count Query

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]



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

Consider nesting the former query with a join clause on Logs in latter query all in an aggregate GROUP BY query.

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

GROUP BY t.[SessionId]
         , t.Username
         , t.Start
         , t.Stop
Comments