SQL query: Get average, and average of everything higher than some value, in same query

Question

I have a column of numbers in a MySQL database, along with a timestamp column and other stuff. I want to know the average of all of the values within a set parameter (say, within the last week), and I also want the average of all of the values that are >= X during that same timeframe. So, if my X is 10, and my values are: 0 0 10 15 20, then I want avg = 9 and avg2 = 15. One way I can do this is run two separate queries, with and without WHERE val >= 10, but I'd like to do it in just one query, if possible. Is there a way of doing this? I've looked at using a CASE, but I can't get the syntax right. Below is the query that I tried:

SELECT AVG(watts), AVG(CASE watts WHEN watts >= 10 THEN watts END CASE) FROM power WHERE meterID = 100 AND time >= '2010-07-29 00:00:00'

Edit: This is what I ended up with.

SELECT AVG(watts) AS avg1,
AVG(IF (watts >= 10, watts, NULL)) AS avg2
FROM power
WHERE meterID = 100 AND time >= '2010-07-30 00:00:00'

Answer 1

Subqueries might be better. You'll only hit the DB once.

SELECT AVG(watts), 
    (SELECT AVG(watts) 
     WHERE meterID = 100 AND time >= '2010-07-29 00:00:00' and watts > 10) as AVG2
WHERE meterID = 100 AND time >= '2010-07-29 00:00:00';

Answer 2

This should work better:

SELECT AVG(watts) AS average_watts,
    AVG(CASE WHEN watts > 10 THEN watts END CASE) AS average_watts_floored
FROM MyTable
WHERE meterID = 100 AND time >= '2010-07-29 00:00:00'

You need a FROM clause if you have a WHERE clause. Actually, you need a FROM clause if you're calculating any kind of AVG() too.

In the CASE, you should leave out the first column name if you're going to have expressions instead of literal values. Refer to syntax documentation.

I recommend using column aliases.

Answer 3

SELECT
   AVG(watts),
   SUM(IF(watts>10,watts,0))/SUM(IF(watts>10,1,0))
FROM FOO
WHERE
   meterID   = 100 and
   time      >= '2010-07-29 00:00:00'

Version 2

SELECT
 AVG(watts),
 IF(SUM(IF(watts>10,1,0))=0,NULL,SUM(IF(watts>10,watts,0))/SUM(IF(watts>10,1,0)))
FROM FOO
WHERE
   meterID   = 100 and
   time      >= '2010-07-29 00:00:00'