Here is a query that groups transactions by price point on a quarterly basis:
SELECT hour(Stamp) AS hour, PointID AS pricepoint, count(1) AS counter
FROM Transactions
GROUP BY 1,2;
Output Example:
+------+------------+---------+
| 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 |
+------+------------+---------+
As you can see, some hours do not have transactions (for example, 7am), and for several hours there are transactions for only one price point (for example, 6am, only price point 20, but there are no transactions for price point 19).
I would like to display the results set with "0" when there are no transactions, and not just be absent, as it is now.
Trying to work with the LEFT OUTER JOIN there. The inHour table contains values of 0..23
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;
This produces the following result (shortened for brevity):
| 5 | 19 | 4 |
| 5 | 20 | 1 |
| 6 | 20 | 2 |
| 7 | NULL | 0 |
| 8 | 19 | 1 |
| 8 | 20 | 4 |
In fact, I would like to:
| 5 | 19 | 4 |
| 5 | 20 | 1 |
| 6 | 19 | 0 |
| 6 | 20 | 2 |
| 7 | 19 | 0 |
| 7 | 20 | 0 |
| 8 | 19 | 1 |
| 8 | 20 | 4 |
"0" , .
! .