title: How to show the rest of periods with no data (with group by and count) - SQL
body: <p>I'm trying to count the records of each period for specific employees. I already removed unnecessary records by using "row_number() over (partition by...", then in the select, above I counted and grouped them to show how many times they exist for a given period number. The output and my SQL looks like this:</p>
  
  <pre><code>SELECT TBL.Name, TBL.Period_ID, count(*) as PeriodCount from (
  Select row_number() over (partition by TBL1.Period_Id, TBL1.Name, TBL1.Date order by 
  TBL1.Period_Id desc) as rn1,  * from ( 
  
  &lt;selected columns&gt;
  ...
  &lt;joins&gt;
  ...
  &lt;where clause&gt;
  ...
  
  ) as TBL1
  )as TBL
  where TBL.rn1 = 1 
  
  Group by TBL.Name, TBL.Period_ID
  order by 1
  </code></pre>
  
  <p>Output:</p>
  
  <p><a href="https://i.stack.imgur.com/0kdNj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0kdNj.png" alt="enter image description here"></a></p>
  
  <p>But what I actually want is to show all the periods for all users even with 0 as counted values. The desired output should look like:</p>
  
  <p><a href="https://i.stack.imgur.com/msXXz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/msXXz.png" alt="enter image description here"></a></p>
  
  <p>I've already tried using the solution with left join</p>
  
  <p><a href="https://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate">How to include &quot;zero&quot; / &quot;0&quot; results in COUNT aggregate?</a></p>
  
  <p>after ")TBL" section but results stay the same.</p>
  
  <p>Can you help me with this, please? :)</p>
  
  <p>Bart</p>