MYSQL question querying total number of hours worked by an administrator

The tables are the following;

MariaDB [test]> select * from AdmWorkHours;
+-------+------------+-------+
| empId | day        | hours |
+-------+------------+-------+
|    17 | 2021-08-15 |  4.00 |
|    17 | 2021-09-18 | 15.00 |
|    25 | 2021-08-17 |  8.00 |
|    38 | 2021-08-20 | 10.00 |
|    64 | 2021-08-31 |  7.50 |
+-------+------------+-------+
5 rows in set (0.001 sec)

MariaDB [test]> select * from Administrator;
+-------+--------------+--------+
| empId | name         | gender |
+-------+--------------+--------+
|    17 | Rishabh Pant | M      |
|    25 | Mary Kom     | F      |
|    38 | Sarah Taylor | F      |
|    64 | Hasan Ali    | M      |
+-------+--------------+--------+

my query has to return the empId , names , and total working Hours for each administrator.

This has been the best query I have come up with my I cannot seem to get the total to sum up per admin.

select a.empId , a.name , sum(distinct h.hours) from Administrator AS a , AdmWorkHours AS h order by h.hours asc;

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use a left join:

SELECT a.empId, a.name, COALESCE(SUM(a.hours), 0) AS hours_worked
FROM Administrator a
LEFT JOIN AdmWorkHours awh
    ON awh.empId = a.empId
GROUP BY a.empId, a.name;

The main problem with your current query is that you are doing a join without any condition, hence it defaults to being a cross join. You are also using the old implicit join syntax. Consider using my version above for best results here.

Method 2

It looks like you forgot the join condition and just need a group by:

select empId, name, sum(hours)
from Administrator natural join AdmWorkHours
group group by empId, name;

You can add the coalesce() that @TimBiegeleisen and left join if you want 0 for when admin had no hours. My solution will just not show those records.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0