How to sum elements of an array?

Question

My problem is two folded. First, I have a table that's not normalized that contains 3 pairs of data (6 columns), which have to be summed. It goes something roughly like this:

|-module_a-|-value_a-|-module_b-|-value_b-|-module_c-|-value_c-|
----------------------------------------------------------------
|   02     |    15   |    nil   |   nil   |   nil    |   nil   |
|   01     |    10   |    00    |   10    |   nil    |   nil   |
|   00     |    25   |    nil   |   nil   |   nil    |   nil   |
|   02     |    22   |    01    |   15    |   03     |   20    |
|   03     |    10   |    nil   |   nil   |   nil    |   nil   |
|   02     |     8   |    00    |   5     |   nil    |   nil   |

My main goal is to get a total sum of each module, but since they can be mixed in 3 fields, it becomes complicated to do it through activerecord (or so I think).

Anyway, to clean that up, I make an array like this:

tok1=Office.select('module_a as module, value_a as value')
tok2=Office.select('module_b as module, value_b as value').where.not(:value_b => nil)
tok3=Office.select('module_c as module, value_c as value').where.not(:value_c => nil)

tok=(tok1+tok2+tok3)

Result:

|-module-|-value-|
------------------
|   02   |  15   |
|   01   |  10   |
|   00   |  25   |
|   02   |  22   |
|   03   |  10   |
|   02   |   8   |
|   00   |  10   |
|   01   |  15   |
|   03   |  20   |
|   00   |   5   |

So far, so good.

Now, I'd like to make a sum of the module values. This would be very simple if I could do a GROUP and SUM via ActiveRecord, but the resulting variable tok is an array, so no ActiveRecord functions are possible. If I try to merge them through tok=tok1.merge(tok2) I end up with the results of tok2 only, which I assume is cause I'm renaming the fields when I query the Office table, I'm not sure. I tried putting them together in different ways, but I get an error that the relations are not immutable.

Anyway, since the result of doing tok=tok1+tok2+tok3 ends up with array, I'd like to do the sum by treating it as an array. I've tried using map, and after doing a tok.map{|h| [h.module, h.value]} I end up with an array like this:

{[02, 15], [01, 10], [00,  25}, [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]}

I don't know how to proceed from here. I tried turning it into a hash, but I'm still too new to the map method so I don't know how to do that exactly. Ideally, I think, it should look like this:

{ ["02" => 15],["01" => 10],["00" => 25],["02" => 22],["03" => 10],["02" => 8],
  ["00" => 10],["01" => 15],["03" => 20],["00" => 5]}

I've read in other questions how to sum the values with a hash/array like that, so if I were to get it like this I think I'd be set.

Sorry for the messy explanation.

`module` is a reserved word in Ruby, so be careful about how you use it. You may get unexpected results or errors trying to access an attribute named `module` (such as used in your SELECT clauses above). — Wizard of Ogz, Oct 20 '14 at 12:55
It's just an example. I don't actually have a field with that name. — dev404, Oct 20 '14 at 12:56

Esse · Accepted Answer · 2014-10-20T12:21:40.530

4

You can do this with something like this:

arr = [[02, 15], [01, 10], [00,  25], [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]]
hash = {}
arr.each do |item|
  hash[item[0]] ||= 0
  hash[item[0]] += item[1]
end
hash # => {2=>45, 1=>25, 0=>40, 3=>30}

or without previous map:

hash = {}
tok.each do |item|
  hash[item.module] ||= 0
  hash[item.module] += item.value
end

Edit: As mentioned in comment by Dima Goltsman (thanks!) it can be rewritten in even shorter form using inject:

arr = [[02, 15], [01, 10], [00,  25], [02, 22], [03,10], [02, 8], [00,10], [01,15], [03,20], [00, 5]]    
arr.inject(Hash.new(0)) do |hash, item|
  hash[item[0]] += item[1]
  hash
end # => {2=>45, 1=>25, 0=>40, 3=>30}

or

tok.inject(Hash.new(0)) do |hash, item|
  hash[item.module] += item.value
  hash
end

edited Oct 20 '14 at 12:21

answered Oct 20 '14 at 11:27

Esse

3,278
2
21
25

Interesting. Could you explain to me what `hash[item[0]] ||= 0` is doing? – dev404 Oct 20 '14 at 11:35
1

It's actually a shortcut for: ````hash[item[0]] || hash[item[0]] = 0````. Therefore if ````hash[item[0]]```` is already present - nothing will happen. If it's nil (or false) - it will be initialized with value 0. It's one of the most common Ruby idioms - you can find a lot of information about it here http://stackoverflow.com/questions/613985/common-ruby-idioms – Esse Oct 20 '14 at 11:37
better to use `inject` method, its a shortcut for what you did – Dima Oct 20 '14 at 12:15

Wizard of Ogz · Answer 2 · 2014-10-20T12:52:13.003

Databases are normally really good at grouping and adding things, so I would probably do as much of this in SQL as possible.

tok = Office.find_by_sql <<-END_SQL
  SELECT module, SUM(value)
  FROM (
    SELECT module_a AS module, value_a AS value FROM offices
    UNION
    SELECT module_b AS module, value_b AS value FROM offices WHERE value_b IS NOT NULL
    UNION
    SELECT module_c AS module, value_c AS value FROM offices WHERE value_c IS NOT NULL
  ) AS modules(module, value)
  GROUP BY module
END_SQL

Evaluates to a collection of Office instances with the following attributes:

+--------+-------+
| module | value |
+--------+-------+
| 1      | 25    |
| 3      | 30    |
| 0      | 40    |
| 2      | 45    |
+--------+-------+

If it were up to me, I would likely create a view with the given SQL query and add an ActiveRecord data model on top of it. That way the Office model would not be used for purposes other than originally intended. Since implementing views varies between DBMS's and branches way off from the original topic I will not go into details here.

How to sum elements of an array?

2 Answers2