Summing values for an overview
I want to get the time used for the event so that I can create an overview.
SELECT query for cases looks like this:
SELECT bc.id, bc.title, bc.estimateCurrent FROM cases bc
In one case, I can get the used time like this:
SELECT SUM(TIME_TO_SEC(TIMEDIFF(dateEnding, dateBeginning))) AS calculatedTime FROM timesheet WHERE `#case` = ?
How do I connect both so that I have one value for a SELECT view query? Basically, I would like the table to look like this:
id | title | estimateCurrent | timeusedinsec 1 | case1 | 20 | 2000 2 | case2 | 40 | 2500 3 | case3 | 70 | 0
Is it possible? I didn't want to have for every request on the php side, which would result in multiple requests. Can help?
+1
a source to share
1 answer
Assuming the schedule column #case
is bc.id, you can use the join like this:
SELECT
bc.id, bc.title, bc.estimateCurrent,
SUM(TIME_TO_SEC(TIMEDIFF(dateEnding, dateBeginning)))
FROM cases bc
JOIN timesheet ts on ts.`#case` = bc.id
GROUP BY bc.id, bc.title, bc.estimateCurrent
GROUP BY defines how SUM () works. Here it will force SUM () to add all lines with the same #case number.
+1
a source to share