title: How to count numbers which are defined in other table, also show zero counts
body: <p>This is the current situation:</p>
  <p>Table1</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>key</th>
  <th>some_id</th>
  <th>date</th>
  <th>class</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>1.1.2000</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>1.1.2000</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>1.1.1999</td>
  <td>3</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm counting the classes and providing the information through a view by using following select statement:</p>
  <pre><code>SELECT key, date, class, count(class) as cnt 
  FROM table1 
  GROUP BY key, date, class
  </code></pre>
  <p>The result would be:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>key</th>
  <th>date</th>
  <th>class</th>
  <th>cnt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1.1.2000</td>
  <td>2</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1.1.1999</td>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>but now there is another table which includes all possible class-codes, e.g.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>parameter_key</th>
  <th>class_code</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>3</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For my view I'm only querying data for <code>parameter_key</code> 1. <strong>And the view now needs to show all possible class_codes, also if the count would be 0.</strong></p>
  <p>So my desired result table is:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>key</th>
  <th>date</th>
  <th>class</th>
  <th>cnt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1.1.2000</td>
  <td>1</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1.1.2000</td>
  <td>2</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>1.1.2000</td>
  <td>3</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1.1.1999</td>
  <td>1</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1.1.1999</td>
  <td>2</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1.1.1999</td>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>but I just can't get my head around how to do this. I've tried to add a <code>right join</code> like this but that does not change anything (probably because I join the class column and do an aggregate which won't be displayed if there is nothing to count?):</p>
  <pre><code>SELECT key, date, class, count(class) as cnt 
  FROM table1 
  RIGHT JOIN table2 on table1.class = table2.class and table2.parameter_key = 1 
  GROUP BY key, date, class
  </code></pre>
  <p>Any idea on how to achieve the desired result table?</p>