title: MySQL left outer join trouble
body: <p>Here is a query that groups transactions by pricepoint on an hourly basis:</p>
  
  <pre><code>SELECT hour(Stamp) AS hour, PointID AS pricepoint, count(1) AS counter
  FROM Transactions
  GROUP BY 1,2;
  </code></pre>
  
  <p>Sample output:</p>
  
  <pre><code>+------+------------+---------+
  | hour | pricepoint | counter |
  +------+------------+---------+
  |    0 |         19 |       5 |
  |    0 |         20 |      14 |
  |    1 |         19 |       3 |
  |    1 |         20 |      12 |
  |    2 |         19 |       2 |
  |    2 |         20 |       8 |
  |    3 |         19 |       2 |
  |    3 |         20 |       4 |
  |    4 |         19 |       1 |
  |    4 |         20 |       1 |
  |    5 |         19 |       4 |
  |    5 |         20 |       1 |
  |    6 |         20 |       2 |
  |    8 |         19 |       1 |
  |    8 |         20 |       4 |
  |    9 |         19 |       2 |
  |    9 |         20 |       5 |
  |   10 |         19 |       6 |
  |   10 |         20 |       1 |
  |   11 |         19 |      10 |
  |   11 |         20 |       2 |
  |   12 |         19 |      10 |
  |   12 |         20 |       3 |
  |   13 |         19 |      10 |
  |   13 |         20 |      10 |
  |   14 |         19 |       8 |
  |   14 |         20 |       3 |
  |   15 |         19 |       6 |
  |   15 |         20 |       8 |
  |   16 |         19 |      11 |
  |   16 |         20 |      10 |
  |   17 |         19 |       7 |
  |   17 |         20 |      17 |
  |   18 |         19 |       7 |
  |   18 |         20 |       9 |
  |   19 |         19 |      10 |
  |   19 |         20 |      12 |
  |   20 |         19 |      17 |
  |   20 |         20 |      11 |
  |   21 |         19 |      12 |
  |   21 |         20 |      29 |
  |   22 |         19 |       6 |
  |   22 |         20 |      21 |
  |   23 |         19 |       9 |
  |   23 |         20 |      23 |
  +------+------------+---------+
  </code></pre>
  
  <p>As you can see, some hours have no transactions (e.g 7am), and some hours only have transactions for a single pricepoint (e.g. 6am, only pricepoint 20 but no transactions for pricepoint 19).</p>
  
  <p>I would like to display the results set with "0" when there are no transactions, rather than just not being there as is the case now.</p>
  
  <p>Trying to work with a LEFT OUTER JOIN there. The <strong>inHour</strong> table contains values 0..23</p>
  
  <pre><code>SELECT H.hour, PointID AS Pricepoint, COALESCE(T.counter, 0) AS Count
  FROM inHour H
  LEFT OUTER JOIN
  (
   SELECT hour(Stamp) AS Hour, PointID, count(1) AS counter
   FROM Transactions
   GROUP BY 1,2
   ) T
  ON T.Hour = H.hour;
  </code></pre>
  
  <p>This produces the following output (truncated for brevity):</p>
  
  <pre><code>|    5 |         19 |     4 |
  |    5 |         20 |     1 |
  |    6 |         20 |     2 |
  |    7 |       NULL |     0 |
  |    8 |         19 |     1 |
  |    8 |         20 |     4 |
  </code></pre>
  
  <p>What I would like in fact would be:</p>
  
  <pre><code>|    5 |         19 |     4 |
  |    5 |         20 |     1 |
  |    6 |         19 |     0 |
  |    6 |         20 |     2 |
  |    7 |         19 |     0 |
  |    7 |         20 |     0 |
  |    8 |         19 |     1 |
  |    8 |         20 |     4 |
  </code></pre>
  
  <p>In my desired output, the value "0" is put next to pricepoints that had no transactions during a given hour.</p>
  
  <p>Your suggestions would be welcome! Thanks.</p>