Difference between normal avg and using case when in Mysql

Question

I have two queries that are supposed to give the same result, however it is not happening.

Can someone explain to me why it is not displayed even result?

Query that returns me the expected result:

SELECT id,
       ROUND(AVG(SinalGPS), 4)
FROM `012017`.gateway
WHERE ID = 8258867
  AND DHEquipamento > '2017-01-22 00:00:00'
  AND DHEquipamento < '2017-01-22 23:59:59'
  AND ignicao = 1
ORDER BY DHEquipamento DESC;

Query that returns me the unexpected value:

SELECT id,
       ROUND(AVG(CASE
                     WHEN DAY(gw.DHEquipamento) = 22
                          AND gw.Ignicao = 1 THEN sinalGPS
                     ELSE 0<br>
                 END), 4) AS '22'
FROM `012017`.gateway gw
WHERE ID = 8258867;

Obs: I need to do the second way, because I need to return IN COLUMNS the average values per day.

Answer 1

Note that in the first section you put as a condition the entire date. Already in the second piece of code, in the case, you filtered only the day of the month. Try with the script below:

SELECT 
    id,
    ROUND(AVG(CASE
                WHEN
                    DAY(gw.DHEquipamento) = 22
                        AND MONTH(gw.DHEquipamento) = 1
                        AND gw.Ignicao = 1
                THEN
                    sinalGPS
                ELSE 0

            END),
            4) AS '22'
FROM
    012017.gateway gw
WHERE
    ID = 8258867;