[SOLVED] Using subqueries to sum an associated model's column returns the same amount for all parents

Issue

Given the models User and Invoice, a user has many invoices and an invoice belongs to a user.

Invoices have a status and amount_cents columns.

I need to write a query that gets all the User columns but also adds the following columns:

  • a total_paid alias column that sums the amount_cents of all paid invoices for each User
  • a total_unpaid alias column that sums the amount_cents of all unpaid invoices for each User

I’m kind of lost as to what the correct structure is when using multiple subqueries that I assign an alias to, but I’ve come up with something pretty basic for the first part of the task:

select users.*, (SELECT SUM(amount_cents) FROM invoices) as total_paid from users
join invoices on users.id = invoices.user_id
where invoices.status = 'paid'
group by users.id

I’m not sure if I should be writing the query from the parent or children side (I suppose from the parent (User) side since all the data I need is in the users column) but the above query seems to be returning the same amounts in the total_paid column for all the different users instead of the right amount for each user.

Any help would be appreciated.

Solution

This can be done using subqueries as follows:

Select users.id,
       (Select Sum(amount_cents) 
        From invoices Where status = 'paid' And user_id=users.id) As total_paid,
       (Select Sum(amount_cents)
        From invoices Where status = 'unpaid' And user_id=users.id) As total_unpaid
From users
Group by users.id

Answered By – Anton Grig

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *