Summarise values in multidimensional array with condition in php/mysql

Question

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

Mohsen Nazari · Accepted Answer · 2021-08-16 09:58:12Z

1

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

answered Aug 16, 2021 at 9:58

Mohsen Nazari

1,3491 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Siam · Accepted Answer · 2021-08-16 09:57:46Z

1

select sum(w1) as w1,xxxxxxxxx from table_name group by e_id, p_id

answered Aug 16, 2021 at 9:57

Siam

333 bronze badges

Collectives™ on Stack Overflow

Summarise values in multidimensional array with condition in php/mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related