Filter and Aggregate a column not in the group by clause MySQL
I don't claim to understand what you are doing, but your logic for p_i is wrong. To get your wanted result:


SELECT 
    name, date_hr, AVG(nor), AVG(p_i)
FROM
    (SELECT 
        name,
            CASE
                WHEN TIME(date_hour) &gt; '12:00:00' THEN DATE(date_hour) + INTERVAL 1 DAY
                ELSE DATE(date_hour)
            END AS date_hr,
            nor,
            validation,
            CASE
                WHEN validation = 1 AND p_i &gt; 0 THEN NULL
                ELSE p_i
            END AS p_i
    FROM
        report) z1
GROUP BY name , date_hr;


name | date_hr    | AVG(nor) | AVG(p_i)
:--- | :--------- | -------: | -------:
SYN  | 2018-02-03 |     18.5 |     null
SYN  | 2018-02-04 |       10 |        3
SYN  | 2020-04-07 |     14.5 |      3.5
YRT  | 2020-04-06 |      136 |     null


db&lt;&gt;fiddle here