Re: Wondering if this makes any sense (Question on SUM())

[laptop alias]

SELECT * FROM ints;
+------+
| i    |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+

SELECT * FROM starts;;
+------------+
| starttime  |
+------------+
| 1249912820 |
| 1249916380 |
| 1249916440 |
| 1249919000 |
| 1249920300 |
| 1249923500 |
| 1249923650 |
| 1249927180 |
| 1249995610 |
| 1249999190 |
+------------+

SELECT (t1.i*10)+(t2.i) x
     , COUNT(starttime) y
 FROM ints t1
 JOIN ints t2
 LEFT JOIN starts s
   ON HOUR(FROM_UNIXTIME(starttime)) = (t1.i*10)+(t2.i) 
GROUP 
   BY x HAVING x < 24;
   
+------+---+
| x    | y |
+------+---+
|    0 | 0 |
|    1 | 0 |
|    2 | 0 |
|    3 | 0 |
|    4 | 0 |
|    5 | 0 |
|    6 | 0 |
|    7 | 0 |
|    8 | 0 |
|    9 | 0 |
|   10 | 0 |
|   11 | 0 |
|   12 | 0 |
|   13 | 0 |
|   14 | 2 |
|   15 | 2 |
|   16 | 2 |
|   17 | 2 |
|   18 | 2 |
|   19 | 0 |
|   20 | 0 |
|   21 | 0 |
|   22 | 0 |
|   23 | 0 |
+------+---+

Your results will be slightly different because you're in a different timezone.



Edited 2 time(s). Last edit at 10/28/2009 04:49AM by laptop alias.