How to get the count and the sum of a value in a column at the same time

Question

Now I have a table like the one below:

Year | Month | Day | Hour | Value
2014   1       1     0      111
2014   1       1     1      222
2014   1       1     2      333
2014   1       1     4      444
                 (no 3:00am on 2014-1-1)
...    ...     ...   ...    ...
2014   1       2     0      555
2014   1       2     1      666
2014   1       2     2      777
2014   1       2     3      888
...    ...     ...   ...    ...

How can I get a list of the average of the values associated with a specific Hour value?

Answer 1

Hmm, you know there is an AVG() function right?

  SELECT hour, AVG(value) avg_value
    FROM table1
GROUP BY hour

If you want the missing hours to count as zero you'll have to do something a bit more fancy. Adding the zeros would probably be a lot easier however.

Answer 2

SELECT AVG(Value), Hour
FROM tableA
GROUP BY Hour