[RESOLVED] sum not working like I wish.

[mbaldwin577]

I am trying to get the sum of the rows that belong to particular account_ids, but I am getting the sum for the complete column, regardless of id. Here is my sql statement.

$sql = 'SELECT a.id, a.name, at.name AS type, sum(t.amount) AS amount FROM accounts a INNER JOIN account_types at ON a.account_type_id=at.id INNER JOIN transactions t ON a.id=t.account_id WHERE t.account_id=a.id';

account_types has id, name.
accounts has id, name, account_type_id
transactions has id, date, account_id, amount

Am I going about this in the completely wrong direction?
Any suggestions on the proper sql statement, or directions to stuff I should read to get a better understanding of what I need would be greatly appreciated.

Thanks,
Michael

[bradgrafelman]

One problem I see is that you're using an aggregate function - SUM() - without any GROUP BY clause. You'll want to GROUP the rows by the account_id.

Next problem I see is that you claim you have "particular account_ids" in mind, yet your SQL query doesn't attempt to match the account_id values against any particular values.

[mbaldwin577]

Thanks, the GROUP BY was exactly what I needed. I guess I did not know I needed to use group by. I have done other queries without it and they worked fine, but they were usually on very simple queries. Like SELECT sum(amount) FROM products.

And in this statement, I guess I do not need the where part either. I thought that is what would tell it to sum the amounts where the t.account_id equaled the a.id

Thanks again.

Michael