SQLite - Returning column total w/all rows
I have the following SQLite table:
transaction_amount transaction_date 50 09/06/2012 50 09/06/2012 75 09/07/2012
I wish to get the total amount for the column, transaction_amount. If I write my SQL query like this:
SELECT transaction_amount,transaction_date, SUM(transaction_amount) AS Total FROM prepaid_tbl WHERE playerID = 17 ORDER BY id DESC
I get the following result:
transaction_amount transaction_date Total 75 09/07/2012 175
I need all three rows returned along with the total. So, if I write the SQL Statement like this:
SELECT transaction_amount,transaction_date,(SELECT SUM(transaction_amount) FROM prepaid_tbl WHERE playerID = 17)Total FROM prepaid_tbl WHERE playerID = 17 ORDER BY id DESC
I now get the results I am looking for:
transaction_amount transaction_date Total 50 09/06/2012 175 50 09/06/2012 175 75 09/07/2012 175
So, my simple question is this the best method/practice for getting my desired results. I am not an SQL expert by any means, so I am still uncertain as to why the first method isn't returning the results I want. Any insight is certainly appreciated.
Thanks, in advance.
You can do cartesian join to solve you problem
SELECT a.transaction_amount, a.transaction_date, b.Total FROM prepaid_tbl a, ( SELECT SUM(transaction_amount) AS Total FROM prepaid_tbl WHERE playerID = 17 ) b WHERE a.playerID = 17