Given the models
Invoice, a user has many invoices and an invoice belongs to a user.
Invoices have a
I need to write a query that gets all the User columns but also adds the following columns:
total_paidalias column that sums the
paidinvoices for each User
total_unpaidalias column that sums the
unpaidinvoices 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.
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)