sqlmysqlwindow-functions

Sum Time Differences over multiple groups in MySQL


I have a table in MySQL...

# id, admin_id, appointment_id, timestamp
'1', '10', '1', '2025-03-01 08:00:00'
'2', '10', '1', '2025-03-01 09:00:00'
'3', '10', '2', '2025-04-01 08:00:00'
'4', '10', '2', '2025-04-01 09:00:00'
'5', '20', '1', '2025-05-01 08:00:00'
'6', '20', '1', '2025-05-01 09:00:00'
'7', '20', '2', '2025-06-01 08:00:00'
'8', '20', '2', '2025-06-01 09:00:00'

What I want to do is sum the min/max time differences for the appointments over the admins.

I can easily group by admins and appointments

SELECT *,
  min(timestamp),
  max(timestamp),
  max(unix_timestamp(timestamp))-min(unix_timestamp(timestamp)) as photo_time,
  count(id) as photo_count
FROM scratch.photo_time
GROUP BY admin_id,appointment_id

Which results in

# id, admin_id, appointment_id, timestamp, min(timestamp), max(timestamp), photo_time, photo_count
'1', '10', '1', '2025-03-01 08:00:00', '2025-03-01 08:00:00', '2025-03-01 09:00:00', '3600', '2'
'3', '10', '2', '2025-04-01 08:00:00', '2025-04-01 08:00:00', '2025-04-01 09:00:00', '3600', '2'
'5', '20', '1', '2025-05-01 08:00:00', '2025-05-01 08:00:00', '2025-05-01 09:00:00', '3600', '2'
'7', '20', '2', '2025-06-01 08:00:00', '2025-06-01 08:00:00', '2025-06-01 09:00:00', '3600', '2'

What I want is a table that is grouped only by admins, with the time differences summed for each appointment.

The closest I have gotten is this...

SELECT *,
  min(timestamp),
  max(timestamp),
  sum((max(unix_timestamp(timestamp)) over (partition by appointment_id))-min(unix_timestamp(timestamp)) over (partition by appointment_id)) as photo_time,
  count(id) as photo_count
FROM scratch.photo_time
GROUP BY admin_id

But this gives an error.

The correct table has only 2 rows with the photo count as 4 and the photo time 7200 for each row.

What is the proper MySQL syntax for this query?


Solution

  • Your first query will be necessary to compute the intermediate (day-by-day) photo_times.
    Then you can just have an upper select SUM() the results of your first query (which acts as a subselect now).

    SELECT admin_id, sum(photo_time)
    FROM
    (
      -- v v v This is your query, untouched:
      SELECT admin_id, appointment_id,
        min(timestamp),
        max(timestamp),
        max(unix_timestamp(timestamp))-min(unix_timestamp(timestamp)) as photo_time,
        count(id) as photo_count
      FROM photo_time
      GROUP BY admin_id,appointment_id
      -- ^ ^ ^ This was your query, untouched.
    ) by_appointment
    GROUP BY admin_id;
    
    admin_id sum(photo_time)
    10 7200
    20 7200

    (and running in a Fiddle)