0

I am trying to get a table that adds all of the values gotten from the query my query on sql looks like this: 

SELECT sum(bmx), sum(amex), sum(bbjio), sum(bancomer), sum(santander), sum(pesos), sum(dolares), sum(gastos),sum(venta_tot) 
FROM avm.avs
WHERE usuario_id = 2;

this works perfectly on mysql, the problem is translating it to rails. I did something like this: 

@er = Av.find_by_sql(["SELECT * FROM avm.avs where created_at between ? and ? and usuario_id in(select id from avm.usuarios where tienda = ?)",@startd, @endd, @nom])

and on my views retrieve the values with the sum method:

<h2>Bancos</h2><hr>
        <h4>Banamex</h4><%= @er.sum(:bmx) %>
        <h4>BanBajio</h4><%= @er.sum(:bbjio) %>
        <h4>Santander</h4><%= @er.sum(:santander) %>

Also tried with the loop <% @er.each do |t| %> but didnt work either.

Any ideas here?

Thanks in advance!

Héctor Monárrez
  • 1
  • 3

2 Answers2

0

Your rails solution is off the mark; try this to get a quick sum of one field:

<%= Av.where(usario_id: 1).sum(:bmx) %>

This will require one statement per sum. That should get you out of this hole! Then, optimize; to get multiple sums it may be best to use AS (select sum(bmx) AS sum_bmx, sum(yy) AS sum_yy) and do @result['sum_bmx']

Also, subselects don't optimize well; stay away from them, just pre-calculate that number in a separate query.

Make sense?

court3nay
  • 2,215
  • 1
  • 10
  • 15
  • this works only with that condition, but how do i add the created_at between ? and ? condition? Thanks! @court3nay – Héctor Monárrez Jan 07 '16 at 02:23
  • You can add more 'where' conditions with a `..` operator -- read up on activerecord! Here's a previous answer; http://stackoverflow.com/questions/2381718/rails-activerecord-date-between – court3nay Jan 07 '16 at 03:54
  • you can chain those 'where's together or put both conditions in there, where(usario_id: 1, created_at: [1.month.ago..1.day.ago]) – court3nay Jan 07 '16 at 03:56
0

You can make it like this:

   @er = Av.select('sum(bmx) as sum_bmx, sum(bbjoi) as sum_bbjoi) ...').first

And then 

   @er.sum_bmx

etc

dsounded
  • 703
  • 5
  • 21
  • this gives me a nil value, just like running it from the find_by_sql query @dsounded – Héctor Monárrez Jan 07 '16 at 02:20
  • @HéctorMonárrez since it doesn't throw an error that table really consists those rows, if return is nil, probably you are trying to sum not number values ? In that case you should use count instead of sum – dsounded Jan 07 '16 at 07:33