title: Oracle SQL: Use distinct(t1.field) in t2 of LEFT JOIN
body: <p>I have a select statement which gives activities over a time range. E.g.</p>
  
  <pre><code>Hour | Action | Count
  ---------------------
   00  | a1     | 23
   00  | a2     | 48
   01  | a1     | 16
   02  | null   | null
   03  | a1     | 5
   04  | a2     | 2
  </code></pre>
  
  <p>You see that thanks to the grouping which yields this result, there is no count for hour 01, action 02 and so on. What I want is the following:</p>
  
  <pre><code>Hour | Action | Count
  ---------------------
   00  | a1     | 23
   00  | a2     | 48
   01  | a1     | 16
   01  | a2     | 0
   02  | a1     | 0
   02  | a2     | 0
   03  | a1     | 5
   03  | a2     | 0
   04  | a1     | 0
   04  | a2     | 2
  </code></pre>
  
  <p>To do that I was thinking of determining the distinct values for row Action and then left join this with the same table. This would be something like this in SQL code:</p>
  
  <pre><code>select distinct(t2.action) as action 
  from t2 as t1 
  left join (select hour, action, count from &lt;whatever to get the table&gt;) as t2 
    on t1.action = t2.action
  </code></pre>
  
  <p>But if I do so, I understandibly get the error that the table t2 is not valid inside the select statement of t1.</p>
  
  <p>Please help me with any advices to get this done. But I don't want to do the distinct on the original table (it has like 50 million entries).</p>
  
  <p>Thanks in advance!</p>