Average time difference by rows with different column value

I have done some searching for similar question, but with no luck. Sample table below.

ID |    MO   | Serial | PiID | Part_Number  |      Timestamp
 1 | F610320 |   1    |  QC1 |  4A130015    | 2017-05-10 09:55:28
 2 | F610320 |   2    |  QC1 |  4A130015    | 2017-05-10 10:12:54
 3 | F610320 |   3    |  QC1 |  4A130015    | 2017-05-10 10:19:47
 4 | F631323 |   1    |  QC2 |  4A110007B   | 2017-05-10 10:20:24
 5 | F631323 |   2    |  QC2 |  4A110007B   | 2017-05-10 10:21:50
 6 | F631323 |   3    |  QC3 |  4A110007B   | 2017-05-10 10:31:02
 7 | F631323 |   4    |  QC3 |  4A110007B   | 2017-05-10 10:31:02

and after the command SELECT A.*, COUNT(A.MO) FROM datalog A GROUP BY A.MO, A.PiID

I want a column with average time. (distinct MO and PiID)

|    MO   | PiID | Part_Number  |COUNT(MO)|    average time
| F610320 |  QC1 |  4A130015    |    3    | (10:19:47 - 09:55:28)/3-1
| F631323 |  QC2 |  4A110007B   |    2    |avg time between row by MO and PiID
| F631323 |  QC3 |  4A110007B   |    2    |avg time between row by MO and PiID

Answer

While MySQL allows some weird combinations of columns between SELECT and GROUP BY clauses, the general rule for standard SQL is “when using aggregation functions, all columns in your SELECT must be either aggregated or placed in GROUP BY too”.

What you’re trying to do then requires a query like this

select  MO, PiID, Part_Number,
        count(*) as CNT,
        TIMESTAMPDIFF(SECOND,
                      min(Timestamp),
                      max(Timestamp)
                     ) / (count(*) - 1) as Average_Time
from    datalog
group by MO, PiID, Part_Number
having  count(*) > 1

This will return the average time in seconds, but you can use different time units as well.

Note that calculating the average time difference requires at least two timestamps to be available, so I added that HAVING clause. Without that, MO / PiID combinations with only one row would cause a division by zero.

Edit

After thinking about it better, actually you don’t need the HAVING clause, because if a MO / PiID combination has 1 row, the min and max will be the same, hence the difference will be 0 and the result of that query will be 0 / 0, which MySQL translates into NULL.

If you prefer something else to be displayed instead of NULL, you can use a coalesce function

select  MO, PiID, Part_Number,
        count(*) as CNT,
        TIMESTAMPDIFF(SECOND,
                      min(Timestamp),
                      max(Timestamp)
                     ) / (count(*) - 1) as Average_Time,
        COALESCE(TIMESTAMPDIFF(SECOND,
                               min(Timestamp),
                               max(Timestamp)
                              ) / (count(*) - 1),
                'something_else') as Average_Time_Coalesced
from    datalog
group by MO, PiID, Part_Number