title: MS SQL Join/Count Query
body: <p>I have the following tables:</p>
  
  <p>Sessions</p>
  
  <ul>
  <li>Id (int)</li>
  <li>UserId (int)</li>
  <li>Start (DateTime)</li>
  <li>Stop (DateTime)</li>
  </ul>
  
  <p>Users</p>
  
  <ul>
  <li>Id (int)</li>
  <li>Username (nvarchar(200))</li>
  </ul>
  
  <p>Logs</p>
  
  <ul>
  <li>SessionId (int)</li>
  <li>LogLevelId (int)</li>
  <li>Timestamp (DateTime)</li>
  <li>Message (varchar(max))</li>
  </ul>
  
  <p>LogLevels</p>
  
  <ul>
  <li>Id (int)</li>
  <li>DisplayText (varchar(5))</li>
  </ul>
  
  <p>What I would like is an output that shows an overview of the list of sessions with the following columns:</p>
  
  <pre><code>SessionId | Username | Start | Stop | [total number of logs from each log level]
  </code></pre>
  
  <p>I have a solution where in C# I:</p>
  
  <ol>
  <li>Select all of the log levels and their associated display text</li>
  <li>Get a list of all sessions using the following query:</li>
  </ol>
  
  <p>-</p>
  
  <pre><code>SELECT [Sessions].[Id]
        ,[Username]
        ,[Start]
        ,[Stop]
        ,[Application]
    FROM [Sessions]
    JOIN [Users] ON [Users].[Id] = [UserId]
  </code></pre>
  
  <ol start="3">
  <li>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:</li>
  </ol>
  
  <p>-</p>
  
  <pre><code>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|
  </code></pre>
  
  <p>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.</p>