SQL query: Get the average value and the average value of everything above a certain value, in the same query

Question

I have a column of numbers in a MySQL database, as well as a timestamp column and other things. I want to know the average value of all values ​​within a given parameter (say, within the last week), and I also want the average value of all values ​​that were> = X during the same timeframe. So, if my X is 10, and my values ​​are: 0 0 10 15 20then I want avg = 9and avg2 = 15. One way to do this is to run two separate queries with and without WHERE val >= 10, but I would like to do this in only one query, if possible. Is there any way to do this? I looked at using CASE, but I cannot get the syntax correctly. The following is the query 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

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'

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'

Answer 2

:

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'

FROM, WHERE. FROM, AVG().

CASE , . .

.

Answer 3

. DB .

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 4

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

"ELSE NULL" <= 10 begint,