CODEWITHSUNDEEP
phpmysqlsql
mwebber
mwebber

Reputation: 386

Best way to sum and seperate by date in MYSQL with/witout php

Hi i have such table information:

example

what i want to do with php with while or just in mysql, is to SUM (time_used) of the rows with status 44 until its reached row with status 55. after that it should begin from start with new summing.

first query should return 37, second 76 (keep in mind it should be universal, for unlimited occurrences of 55 status row)

i thought of a way with time/date filtering and have this: 

select sum(time_used) as sumed 
from timelog 
where start_time > (select end_time from timelog where (status='55') 
ORDER BY id DESC LIMIT 1) ORDER BY id DESC

but this works only for last combination of 44 and 55

i know i will need two way filtering( < end_time and > end_time) so it will work for all cases, but cant think of a way to do it in php

can anyone help me?

EDIT: sqlfiddle whoever want it:

http://sqlfiddle.com/#!2/33820/2/0

Upvotes: 1

Views: 189

Answers (3)

Lorenz Meyer
Lorenz Meyer

Reputation: 19895

There are two ways to do it: Plain SQL or PHP. If you treat thousands of rows, it may be interresting to choose between the two by testing performance.

  1. Plain SQL

    select project_id, task_id, user_id, sum(time_used) as time_used,
min(start_time) as start_time, max(end_time) as end_time, max(comment) as comment from
    (select t.id, t.project_id, t.task_id, t.user_id, t.time_used, 
    count(t2.id) as count55, t.start_time, t.end_time, t.comment
    from timelog t
    left join timelog t2 on t.id>t2.id and t2.status=55 and t.task_id=t2.task_id
    group by t.id) as t
group by count55;

    I assume here that a task can belong to one user only

  2. SQL and PHP

    $link = mysqli_connect( ... );
$query = "select id, project_id, task_id, user_id, time_used, start_time, end_time, status
     from timelog order by id";
$result = mysqli_query($link, $query);
$table = array();
$time_used = 0;
$start_sum = true;
$i = 0;
while($row = mysqli_fetch_assoc ($result)){
    if($start_sum){
        $table[$i] = $row;
        $start_sum = false;
    } else {
        $table[$i]['time_used'] += $row['time_used'];
        $table[$i]['end_time'] += $row['end_time'];
    }
    if($row['state'] == 55){
        $i++;
        $start_sum = true;
    }
}

If two tasks can run in simultaneously, solution 1 will work, but solution 2 will need to be adapted in order to take this in account.

Upvotes: 2

danisius
danisius

Reputation: 597

here is my intepretation: http://sqlfiddle.com/#!2/33820/45

  set @n=0;
    select project_id, task_id, user_id,sum(time_used)  from (
    SELECT  time_used,project_id, task_id, user_id,
            @n:=if(status=55,@n+1,@n),
            if(status=55,-1,@n) as grouper FROM timelog
    ) as t
    where grouper>-1
    group by grouper;

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

I'm neither a php nor MySQL programmer, but I can explain the logic you want to follow. You can then code it.

First, query your db and return the results to php.
Next, set two sum variables to 0. Start looping through your query results. Increment the first sum variable until you reach the first row that has status 55. Once you do, start incrementing the second variable.

The tricky part will be to sort your query by the row number of the table. Here is a link that will help you with that part.

Upvotes: 1

Related Questions