it sounds a bit complicated and I am struggling with a query. Hopefully you can help me out.

What I need is to merge by name or date, the count and time column should be sum (02:05:00) and aht column should be average of both values.

I didnt found a solution, as all comes up with a group by view, but I need to have the data in one row and the second row deleted...

CodePudding user response：

Selecting the SUM and AVERAGE is not hard, see below fiddle:

SELECT
   name,
   `date`,
   sum(`count`) as 'count',
   SEC_TO_TIME(sum(TIME_TO_SEC(`time`))) as 'time',
   SEC_TO_TIME(avg(TIME_TO_SEC(aht)))  as aht
FROM table1
GROUP BY
   name,
   `date`;

DBFIDDLE

output:

You can add this new row, and after this delete all rows that have a count >=2 when grouped by name and date.