basic sql : selecting AVG() values from the same column multiple times in one query, when each wanted AVG() value uses different WHERE clause

Question

I want to get three different average values from one column (value_to_count) inside one table where all of those average values has a different WHERE clause (according to time).

Example Data:

###services#### Table
service_id       value_to_count                time
-----------      -----------------------       ---------
     604                    2054               04:04:50
     604                    3444               05:00:15
     604                    2122               07:12:50
     604                    2144               09:10:50
     604                    2001               13:12:53
     602                    2011               15:00:12
     602                    2115               17:22:35
     602                    1411               20:22:12
     602                    1611               21:04:52
     602                    2111               23:43:45

I'm using this query at the moment to get the average value on time between 18 and 23:

Query

SELECT 
service_id AS service, AVG(value_to_count) AS primetime 
FROM services 
WHERE HOUR(time) BETWEEN 18 AND 23 
GROUP BY service_id

And it gives me this kind of results:

### Result #### 
service          primetime
-----------      --------------      
     604               2154           
     602               2444           

Now I want to get other average values next to the one I already got. This time I just want to get averages by 'HOUR(time) BETWEEN 06 AND 18' and 'HOUR(time) BETWEEN 23 AND 06' aswell.

This is the form of result I want to get:

### Wanted Result #### 
service          primetime          other_time_interval_1   other_time_interval_2
-----------      --------------     ----------------        ------------------
     604               2154              2352                      1842
     602               2444              4122                      1224

Answer 1

This should do it:

SELECT service_id AS service, 
       AVG(case when HOUR(time) BETWEEN 18 AND 23 then value_to_count else null end) AS primetime,
       AVG(case when HOUR(time) BETWEEN 06 AND 18 then value_to_count else null end) AS other_time_interval_1
FROM services 
GROUP BY service_id