CODEWITHSUNDEEP
mysql
Jeroen Bellemans
Jeroen Bellemans

Reputation: 2035

sum of table with join on joined table gives wrong total

I have three tables:

  1. users_timings_log
  2. projects_tasks
  3. projects_modules

I want to calculate the total hours spent on a task with module_ id 2 for example

I use the following query for it:

SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, `start_date`, `end_date`)))
AS `total_hours`
FROM `users_timings_log`
INNER JOIN `projects_tasks` ON users_timings_log.type_id = projects_tasks.id
LEFT JOIN `projects_modules` ON projects_tasks.module_id = '2'
WHERE `type` = '0'

This is the structure of my users_timings_log table

+-------+---------------------+---------------------+------+---------+
|   ID  | start_date          | end_date            | type | type_id |
+-------+---------------------+---------------------+------+---------+
|   1   | 2015-09-17 09:00:00 | 2015-09-17 19:00:00 | 1    | 28      |
|   2   | 2015-09-17 07:00:00 | 2015-09-17 12:00:00 | 1    | 24      |
|   3   | 2015-09-17 08:00:00 | 2015-09-17 16:15:00 | 1    | 18      |
|   4   | 2015-09-17 10:00:00 | 2015-09-17 17:00:00 | 1    | 24      |
+-------+---------------------+---------------------+------+---------+

Here's the structure of my projects_tasks table

+-------+---------------+-----------------------------+-----------+
|   ID  | name          | description                 | module_id |
+-------+---------------+-----------------------------+-----------+
|   18  | First task    | Some useless description    | 1         |
|   24  | Second task   | Another useless description | 2         |
|   28  | Third task    | NULL                        | 3         |
+-------+---------------+-----------------------------+-----------+

Structure of projects_modules table

+-------+---------------+
|   ID  | name          |
+-------+---------------+
|   1   | Module 1      |
|   2   | Module 2      |
|   3   | Module 3      |
+-------+---------------+

What I need is the total of hours of a specific module, but for some reason It is giving me for each module the same amount of time.

I'm stuck on it. What am I doing wrong?

Thanks in advance

Upvotes: 0

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You need a GROUP BY and proper JOIN:

SELECT pt.module_id,
       SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, ul.`start_date`, ul.`end_date`)))
AS `total_hours`
FROM `users_timings_log` ul INNER JOIN
     `projects_tasks` pt
     ON ul.type_id = pt.id 
WHERE `type` = '0'
GROUP BY pt.module_id;

If you only want one module, then add a WHERE clause for that purpose.

I also added table aliases, so the query is easier to write and to read.

I should add, the problem with your query is that it did not have proper JOIN conditions for projects_modules. You don't seem to need that table, so removing the table is fine. If you need columns from that table, add the appropriate JOIN conditions.

Upvotes: 1

Related Questions