Difference between normal avg and using case when in MySQL

Question

Difference between normal avg and using case when in MySQL

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 same result?

Query which 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 which returns me the value not expected:

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;

Note: I need to do the second way, because I need to return the values of the averages per day in columns.

1

mysql case select

Author: Marconi, 2017-02-10

Source

1 answers

score 1 · Answer 1

Note that in the first excerpt you put the entire date as a condition. Already in the second snippet 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;