CODEWITHSUNDEEP
sqlruby-on-railspostgresqljoinsubquery
Dave
Dave

Reputation: 11862

SQL joining, summing from sub queries to calculate a total aggregate amount

I've done all of this in active record, but it's not going to work with pagination so I'd like to switch to a SQL query with OFFSET so I can query efficiently. - Rather than getting User.all, then working out the calculations on related objects and then compiling it all into a bundled array and finally sending it up to the view I'd like to handle the calcs in a find_by_sql command so it's easier to manage pagination etc.

Trying to work out a users total amounts invested + their residual uninvented amounts in my little stock market simulator I'm playing with.

Have a share prices table that has multiple entries for each new share price for each company, so want to:

Trying to get that total valuation per customer and order by 'richest' user:

select u.id,
sum( 
    ((
  select s.price from shareprices s
  WHERE s.company_id = o.company_id and u.id = o.user_id
  ORDER BY id DESC LIMIT 1) * o.shares_owned
    ))
    + u.dollars as totalAmount
from users as u full outer join ownerships as o on u.id = o.user_id group by u.id order by totalamount ASC

That returns fine for where there are ownerships and the calculations for total invested work out, but for users who only have uninvested dollars how can i get them to show in that summed column, so essentially its 0 (ie. no owned investment amounts because they own shares in no companies) + u.dollars to get how much they have, but I don't understand how to make SQL do that.

I am hoping to avoid needing a pgsql function() to achieve this logic but if the above didn't make it obvious, I'm terrible at SQL and only learning it now. I hope my explanation is even understandable!

Upvotes: 1

Views: 442

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52675

You can add a colaesce around the part of the calculation that needs to treat nulls as zeros (its not clear to me which part needs to )

sum(COALESCE 
    ((
  select s.price from shareprices s
  WHERE s.company_id = o.company_id and u.id = o.user_id
  ORDER BY id DESC LIMIT 1) * o.shares_owned
    )) , 0))
    + u.dollars totalAmount

Upvotes: 1

Related Questions