I'm having difficulty with
GROUP BY in MySQL.
My database setup:
client_visit - id - member_id - status_type_id (type_of_visit table) - visit_starts_at - visit_ends_at member - id schedule_event - id - member_id - starts_at - ends_at type_of_visit - id - type (TYPE_BOOKED, TYPE_PRESENT etc)
For the purpose of this question: a
member teaches a class or leads an activity (a
schedule_event) at a given time. A
client signs up for this class or activity.
Client A, B and C book visits and those go to
client_visit table which consists of
member_id, so we know what class and what member is teaching/or having activity.
Now, we want to know the total time a given member spent teaching/leading events that clients signed up for (based on the
type_of_visit column equivalent to "Booked" or "Present"). We'll take member ID 82 as our test case.
Member ID 82 had 4 clients in two different classes, so if each class took 2h 15 minutes (8100 seconds), that means total time should be 16200 seconds.
Here's my query first:
SELECT cv.member_id AS `member_id`, sch.id AS `scheduleId`, cv.visit_starts_at AS `visitStartsAt`, TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at) AS `totalTime` FROM `schedule_event` AS `sch` LEFT JOIN `client_visit` AS `cv` ON cv.schedule_event_id = sch.id INNER JOIN `type_of_visit` AS `tov` ON tov.id = cv.status_type_id WHERE (tov.type = 'TYPE_BOOKED' OR tov.type = 'TYPE_PRESENT') and cv.member_id = 82
This shows me the clients for the first class, and the one for the second. I just want two rows, one for each class. So, I add this:
GROUP BY sch.id
So far so good,
I know that there are two schedule ids for this member, so I modified the group by to pull those together into one:
GROUP BY sch.id AND cv.member_id
I expect that it will first merge based on
sch.id (the result it's already shown in the picture above) and
cv.member_id (we got two rows, so after merging, should be one)
Now, just like I pulled together the two schedule IDs, I want to add up the time for the two scheduled classes.
I modify the query now:
SUM(TIMESTAMPDIFF(SECOND, sch.starts_at, sch.ends_at)) AS `totalTime`
I got 32400! For some reason, the SUM is still seeing all 4 rows instead of just the unique 2.
I expected that the final result would be
+-----------+------------+ | member_id | total_time | +-----------+------------+ | 82 | 16200 | +-----------+------------+
All other columns are not needed, I just made them to see what is happening