moessi774 moessi774 - 3 months ago 13
MySQL Question

MySQL query for getting all foreign keys and the sum of all their columns in m:n table

I have got a mySql table where employees and their work hours on a specific day are stored in. I tried to write a query where i get all

employee_id
's with their sum of hours of work in a calendar week and I don't have another idea how to do it.

This is what it should look like:

employee_id | hours(sum)

1 | 21.5

2 | 28

3 | 3

I tried it with this statement:

select employee_id, sum(hours) from xxx.hours_of_work where week(date_of_work)=weekofyear('<date_inside_one_calendar_week>')


But it only returns:

employee_id | hours(sum)

1 | 52.5

Table

1]

Entries

2]

JPG JPG
Answer

You need GROUP BY to do aggregation :

SELECT employee_id, sum(hours) 
FROM xxx.hours_of_work 
WHERE week(date_of_work) = weekofyear('<date_inside_one_calendar_week'>)
GROUP BY employee_id