I have the following table. I'm using mysql-5.7. Here is the dbfiddle
select * from report;
+----+---------------------+---------+------+------+------------+------+-------------------+
| id | date_hour | speed | name | nor | validation | p_i | speed_performance |
+----+---------------------+---------+------+------+------------+------+-------------------+
| 1 | 2018-02-03 13:23:00 | -12.3 | SYN | 10 | 1 | 3 | 4 |
| 2 | 2018-02-03 11:23:00 | -6.36 | SYN | 13 | 0 | 4 | 5 |
| 3 | 2018-02-03 01:23:00 | -26.36 | SYN | 24 | 0 | 2 | 4 |
| 4 | 2020-04-06 21:23:00 | -156.36 | SYN | 16 | 1 | 3 | 6 |
| 5 | 2020-04-06 03:23:00 | -36.36 | YRT | 136 | 0 | 2 | 5 |
| 6 | 2020-04-06 12:23:00 | -16.36 | SYN | 13 | 1 | 4 | 4 |
+----+---------------------+---------+------+------+------------+------+-------------------+
I would like to group by
date_hour
such that after 12:00:00
, it counts as the next day. The p_i
column should be averaged based on this condition: take average
of p_i
where validation
is 1 and p_i
is non-zero. The values should be averaged based on the new version of date_hour
. Additionally, another column (nor
) is averaged without such a condition.
The output should look like this
+------+------------+----------+------+
| name | date_hr | avg(nor) | 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 |
+------+------------+----------+------+
I tried the following query. I had to disable only_full_group_by
to get it to work:
select name,
case when time(date_hour) > '12:00:00'
then date(date_hour) + interval 1 day
else date(date_hour) end as date_hr,
avg(nor),
case when validation = 1 and p_i > 0 then avg(p_i) else null end as p_i
from report group by name, date_hr;
+------+------------+----------+------+
| name | date_hr | avg(nor) | p_i |
+------+------------+----------+------+
| SYN | 2018-02-03 | 18.5 | 3 |
| SYN | 2018-02-04 | 10 | NULL |
| SYN | 2020-04-07 | 14.5 | NULL |
| YRT | 2020-04-06 | 136 | 2 |
+------+------------+----------+------+
I had to disable only_full_group_by to get it to work:
- a query which runs under that condition is a broken query - there's a reason whyonly_full_group_by
is now the default in MySQL! Can you please provide your table structures as DDL and your data as DML - better yet, put them into a fiddle (but be sure to keep them here also!). p.s. welcome to the forum!