Sum Time Differences over multiple groups in MySQL

Question

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?

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
– 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
– 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
– Akina, Commented Mar 6 at 4:49

Guillaume Outters · Accepted Answer · 2025-03-06 00:43:26Z

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)

answered Mar 6 at 0:43

Guillaume Outters

7,5371 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum Time Differences over multiple groups in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related