CODEWITHSUNDEEP
mysqlsql
Dong Nguyen
Dong Nguyen

Reputation: 1269

Select query with minus and sum

There are 3 tables: user, project and tracking. Here is the table tracking:

id user_id project_id start_time end_time
123 32 12 8 12
124 32 12 13 17
125 77 63 10 11
126 77 12 13 17
127 15 12 15 17
128 24 12 8 10

The fields start_time and end_time are relevant to working time (8 to 12, 13 to 17). Every day, a user can contribute to many projects, of course.

Now I would like to get the project info as below:

Project name Total tracking time
First 1860
Second 3122

In which, Total tracking time is the sum of all the time the project members have been tracked as contributor.

How I can get this result with a select query? The following doesn't work.

SELECT *,
  SUM(SELECT *, (end_time - start_time) AS tracking_time,
    FROM tracking
    WHERE project_id = project.id
  )) AS tracking_time,
FROM project

Upvotes: 0

Views: 82

Answers (2)

id'7238
id'7238

Reputation: 2593

Your query should be like this:

SELECT p.*, t.tracking_time
FROM project p
JOIN (
  SELECT project_id, SUM(end_time - start_time) AS tracking_time
  FROM tracking
  GROUP BY project_id
) t ON p.project_id = t.project_id

Or a corrected query, as you tried, will also work:

SELECT *,
  (SELECT SUM(end_time - start_time) AS tracking_time
   FROM tracking
   WHERE project_id = project.id
  ) AS tracking_time
FROM project

Upvotes: 2

Jose7198
Jose7198

Reputation: 91

Assuming the column names of the project table, I think the following query can help:

SELECT 
    name as 'Project name', 
    SUM(end_time - start_time) as 'Total tracking time'
FROM project p JOIN tracking t
ON p.id = t.project_id
GROUP BY name

Upvotes: 1

Related Questions