DISCTINCT SUM()

[kathleennewbie]

Having a really hard time with this...

 

 
$get_total_income = mysql_query("SELECT SUM(tAmount) FROM transactions WHERE tStatus = 1 AND tUserType = 1", $db2) or die("Query error: ". mysql_error());
               $total_income = number_format(mysql_result($get_total_income,0),2);

 

The problem is that it is summing all transactions, where say a member will be on auto-bill each month for 29.95 and we only want to show what the total monthly residual is, not the total overall income combined of all months... so we need to select just the latest transaction from each member

 

EXAMPLE:

tUserIDtAmountunixtime

119.95

214.95

329.95

139.95

219.95

529.95

129.95

 

USER ID 1 has 3 payment records (date filed is also in the table) We need to show the latest payment (so in between two dates) and add all the paymetns together.

 

So latest payment for user ID 1 is 19.95 (current monthly payment) plus (+) user ID 2 is 14.95 (current monthly payment) + userID 3 is 29.95 (current monthly payment) + user ID 5 is 29.95 (current monthly payment).

 

So we want to select the last inserted record of each user and sum them all together, skipping user duplicates or previous entered records.

 

select

 

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html