Sum values in multidimensional array (selecting multiple columns with multiple rows)

Question

Let's say I have 5 different columns, a, b, c, d, e, and I'm selecting multiple rows:

$result = mysqli_query($conn,"SELECT a,b,c,d,e FROM posts WHERE submitter='$user'");

while ($row = mysqli_fetch_assoc($result)){
  $ratings[] = $row; 
}

Example:

The user has 3 posts, so it'll select 3 rows in the query.

I want to sum all of the rows' values for a (and the rest of course).

e.g.

row 1 a value = 4

row 2 a value = 10

row 3 a value = 1

So I need to sum all of those to get 15.

I know to use array_sum($ratings) to find the sum of the array but only if you select one column (a) which can have multiple rows, but this is multi-dimensional right due to multiple column values being selected?

possible duplicate of [MySQL Sum() multiple columns](http://stackoverflow.com/questions/22369336/mysql-sum-multiple-columns) — Blue, Jun 14 '15 at 06:45

score 0 · Accepted Answer · answered Jun 14 '15 at 06:46

0

You can just use sum in your query:

select sum(a)
     , sum(b)
     , sum(c)
     , sum(d)
     , sum(e)
from posts
where submitter = '$user'

answered Jun 14 '15 at 06:46

potashin

44,205
11
83
107

What will be stored in the `$ratings` array? – frosty Jun 14 '15 at 06:50
I think you read that wrong.. -What- will be stored in there I mean? – frosty Jun 14 '15 at 06:51
@frosty: sorry, there will be an array of sums for each column, you can `var_dump` result to see it yourself. – potashin Jun 14 '15 at 06:52
Thanks, I think it's still producing a multi-dimensional array tho. `array(1) { [0]=> array(5) { ["sum(a)"]=> string(2) "22"` --- how do I retrieve the "22"? – frosty Jun 14 '15 at 06:55
@frosty: well, just use `$ratings = mysqli_fetch_assoc($result)` instead of the whole `while` loop. Then you can get `22` with `$ratings["sum(a)"]`. – potashin Jun 14 '15 at 06:57
Ahh, so it sums all the rows into "one" row kinda? – frosty Jun 14 '15 at 06:57
@frosty: yes, it is the main idea here. – potashin Jun 14 '15 at 06:57
1

Yes, just saw your edit. Was trying $row[0] and $row['a'] but wasn't working, $row['sum(a)'] works though – frosty Jun 14 '15 at 07:03

score 0 · Answer 2 · edited Jun 14 '15 at 08:18

0

You can use count aggregate function and group by in MySQL.

SELECT
  submitter,
  count(a) as rating
FROM posts
WHERE submitter='$user'
GROUP BY submitter

A a result you will get something like that:

some submitter, 3
another submitter, 10
one more submitter, 1

Is this helpful?

edited Jun 14 '15 at 08:18

enrico.bacis

30,497
10
86
115

answered Jun 14 '15 at 06:46

Aleksei Akireikin

1,999
17
22

ty for the answer, I was able to get it working with the other answer tho – frosty Jun 14 '15 at 07:04

Sum values in multidimensional array (selecting multiple columns with multiple rows)

2 Answers2