sql - Sum Time Differences over multiple groups in MySQL - Stack Overflow

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?

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?

• sql
• mysql
• window-functions

Share Improve this question Follow edited Mar 6 at 0:31 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 6 at 0:23 AQuirkyAQuirky 5,27622 gold badges3939 silver badges6060 bronze badges 3

• Please include the error message in the question. If you search for that error message, you'll find past questions that show how to fix it. – Barmar Commented Mar 6 at 0:26
• You can't embed an aggregate function inside a window function... what does that actually mean? – Dale K Commented Mar 6 at 1:43
• But this gives an error Of course. Window functions are evaluated after GROUP BY whereas you use them in aggregate function argument which needs to evaluate them before grouping. This causes an error. – Akina Commented Mar 6 at 4:49

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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)