Using SUM to find the total amount in columns and then group

Question

I have a list of amounts in my transactions table. I want to know the total transaction amount for each person_id that has a total amount greater than 50 .

I was hoping this would work, but it is not:

 SELECT ( SELECT SUM(amount) FROM transactions WHERE person_id = p.id ) AS total_amount FROM people AS p WHERE total_amount > 50 

The only way to get this to work:

 SELECT ( SELECT SUM(amount) FROM transactions WHERE person_id = p.id ) AS total_amount FROM people AS p WHERE ( SELECT SUM(amount) FROM transactions WHERE person_id = p.id ) > 50 

.. This is super inefficient. Any suggestions on how best to format my request?

Answer 1

Try

 SELECT person_id, SUM(amount) FROM transactions GROUP BY person_id HAVING SUM(amount) > 50 

SQLFiddle

UPDATE: people and transactions joined

 SELECT t.person_id, p.name, SUM(t.amount) amount FROM transactions t JOIN people p ON t.person_id = p.id GROUP BY t.person_id, p.name HAVING SUM(t.amount) > 50 

SQLFiddle