Any which way but?

[knighty]

I have 3 tables - projects (lists projects), tasks (lists tasks), log (records time for projects and tasks)

I have a page that lists all the work and shows total time spent

The log table looks like :

logID ¦ jobID ¦ type ¦ start ¦ end ¦ total
1 23 T 9:00 10:00 1:00
2 23 P 10:00 12:00 2:00

If I GROUP BY jobID it will just bring back 1 row, I cant find anything on clever GROUP statments except something about rollup

I was looping through everything but that has its own problems.

*note - yes I know it wont add 2:00 (its VARCHAR at the mo, I would probably change to DECIMAL)

Is there another approach to this?

[coopster]

It seems as though you want a total amount of hours displayed per project. The key is to use the

GROUP BY

function,

SUM

:

SELECT 
 jobID, 
 SUM(total) AS total 
 FROM log_table 
 GROUP BY jobID 
;

[knighty]

Yeah, thats what I wanted BUT there can be 2 jobIDs that are the same.

I could have taskID=34(in tasks table) and projectID=34 (in projects table)

In the log table it would be jobID=34 and type='T' (for task) jobID=34 and type='P' (for project)

just grouping jobID would sum the 2 jobID 34 jobs as one!

something like sum(jobID) where type='p' would be nice :)

[coopster]

You said it, now just add the

WHERE

clause to your query:

SELECT 
 jobID, 
 SUM(total) AS total 
 FROM log_table 
 WHERE type = 'P' 
 GROUP BY jobID 
;

[ara818]

If I understand correctly you want this:

select job_id, type, sum(total)
from logs
group by job_id, type

This would would give you one row for every (job_id, type) so you wont have to run a seperate query for each job type.