Difference between normal avg and using case when in MySQL

Question

Difference between normal avg and using case when in MySQL

Navigation

#1 by (1 votes)

1

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

Can someone explain to me why it does not show the same result?

Query that returns 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 form, since I need to return the values of the averages per day in COLUMNS.

mysql select case

asked by anonymous 10.02.2017 / 18:29

1 answer

Improve performance for predictive model creation How to access a non-explicit function

score 1 · Answer 1

Note that in the first snippet you have placed the entire date as a condition. In the second code snippet, in the case, you filtered only the day of the month. Try 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;