Add SUM from table A to a single column of table B

Question

Simplified terms:

Table A
ID | City | Amount
1 | Atlanta | 100
2 | Atlanta | 200
3 | Utah | 300

Table B
ID | City | Modifier
1 | Atlanta | 50
2 | Utah | 60

Steps:

Calculate Atlanta
SUM of Amount from Table A:

100 + 200

Add Modifier to result of #1:

50 + ( 100 + 200 )

Output:

350

edit:

I can only get the sum of amount of Table A. I don't know how to add the modifier without adding it to each record of Table A:

TableA.joins("
  LEFT JOIN TableB ON TableB.city = TableA.city
").select("
  SUM(TableA.amount) + TableB.modifier
").where("
  TableA.city = 'Atlanta'
").group("
  TableA.city
")

So what have you tried? – SteveTurczyn Oct 02 '18 at 08:21 — SteveTurczyn, Oct 02 '18 at 08:21

score 2 · Accepted Answer · answered Oct 02 '18 at 17:16

It's still not clear what you need. Moreover, using Arel methods chain for such things make them more complex.

If you want to have a list of cities with the sum of amounts from TableA plus modifier from TableB, then it can be obtained with a single SQL-query.

(Assuming you're using PostgreSQL):

SELECT T.city, COALESCE(T.amount, 0) + COALESCE(B.modifier, 0) AS modified_sum
FROM (SELECT city, SUM(amount) AS amount FROM table_a GROUP BY city) T
    FULL JOIN table_b B ON B.city = T.city;

http://rextester.com/OSS97422

If you have models TableA and TableB then the query could be converted to a Rails method:

TableA.find_by_sql "SELECT T.city, COALESCE(T.amount, 0) + COALESCE(B.modifier, 0) AS amount
    FROM (SELECT city, SUM(amount) AS amount FROM #{TableA.table_name} GROUP BY city) T
        FULL JOIN #{TableB.table_name} B ON B.city = T.city"

If your task is to get value for 'Atlanta', then the method call will be:

TableA.find_by_sql ["SELECT T.city, COALESCE(T.amount, 0) + COALESCE(B.modifier, 0) AS amount
    FROM (SELECT city, SUM(amount) AS amount FROM #{TableA.table_name} GROUP BY city) T
        LEFT JOIN #{TableB.table_name} B ON B.city = T.city
    WHERE T.city = :city", { city: 'Atlanta' }]

Please note FULL JOIN in the first case and LEFT JOIN in the latter. For 1st query FULL JOIN provides cities from both tables, A and B. For 2nd call the query looks for 'Atlanta' in TableA.

If your task is different, you can modify these calls as you need.

score 1 · Answer 2 · answered Oct 02 '18 at 17:00

The question can be answered fairly easy using raw SQL:

with amount_by_city as (
select
         city
       , sum(amount) as total_amount
    from table_a
group by city
)
select
    b.city
  , a.total_amount + b.modifier
from table_b b
join amount_by_city a on b.city = a.city

RoR can run a raw SQL statement like documented in Rails raw SQL example . Unless you have other needs, just use the raw SQL.

SQLfiddle

Add SUM from table A to a single column of table B

2 Answers2