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.

Answers


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 

SQLFiddle Demo


Need Your Help

Installed application doesn't have write permissions anywhere

c# permissions wix installation

I have created a C# application, not having in mind that all debugging were done using administrator rights as I always use in Windows.

Is JSCocoa safe for commercial Mac desktop applocations?

javascript cocoa osx

If I were to write a commercial (=sold for money) app for Mac OS X (not iPhone) would it be wise to do so in Javascript with JSCocoa? I know Javascript by heart but next to nothing about Objective-...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.