Search code examples
phpmysqlmultidimensional-arrayarray-sum

Summarise values in multidimensional array with condition in php/mysql

I have the following table in mysql

e_id p_id w1 w2 w3 w4
87 1019 1 1 0 0
87 1019 0 0 0 1
87 1020 1 1 1 1
87 1021 0 1 0 0
87 1021 0 0 1 1
87 1021 1 0 0 0
89 1020 1 1 1 1
89 1022 1 1 1 0
89 1022 0 0 0 1

I want to sum rows where e_id and p_id are equal. So the result should be like this:

e_id p_id w1 w2 w3 w4
87 1019 1 1 0 1
87 1020 1 1 1 1
87 1021 1 1 1 1
89 1020 1 1 1 1
89 1022 1 1 1 1

I'm not sure if I can do that in mysql, probably only in php. After fetch I got the following array in php

$schedules[]= array('employee' => $e_id, 'project' => $p_id, 'weeks' => $weeks);


$e_id = fetched e_id from table
$p_id = fetched p_id from table
$weeks = fetched array of w1..w4 from table

How would I do it? Appreciate any help

Solution

  • You should apply group by on two columns. It should be like this:

    SELECT
    e_id,
    p_id,
    SUM(w1) as w1,
    SUM(w2) as w2,
    SUM(w3) as w3,
    SUM(w4) as w4
FROM schedule
GROUP BY e_id, p_id