Here is the query I came up with to give me a total of how many gold account payments I have in the database as well as the current value of those payments. When I run this, it gives the correct total of 34. But 34 x $15.00 each is $510. But for some reason the value is showing $1470.

SELECT count(distinct uID) as total, sum(amount) as value from payments where type = ‘Gold’ and frequency = ‘Monthly’ and uID in (select uID from users where subscription = ‘Gold’)

I am really stumped on why the sum is not working correctly. Any ideas?

This table records every payment received by the monthly paypal subscription that my users subscribe to. So yes, there will be several rows in the payment table for a given user. That is why I used distinct in my query. So the total of unique paying users is correct, 34, I just need to figure out why it’s not correctly calculating 34 users x $15 each payment.

looks like you only have about a hundred rows that fit the WHERE clause

instead of aggregating, remove the GROUP BY clause, specify SELECT uid, amount, paymentdate, and then browse the results, inspect them carefully, you will find the extra rows

Ok, I removed count(distinct uID) from the query and now see the additional rows. I’m still puzzled why distinct really didn’t work. Take for example uID 732. There was a row in there for the months of April, May and June. Is this query going to require the use of MIN or MAX to either find the oldest or most recent row for a given uID first before distinct will really work?

r937, I’m really stuck on this one. Is this query going to require the use of MAX to find the oldest row for a given uID first before distinct will really work? The goal of this query is see how much the recurring monthly income is based on all of the payments recorded in the table. We should only be counting one row per uID. I don’t need a running total of how much I ever made, rather, how much income can be anticipated based on the number of people who are subscribed for the monthly subscription. Does this help?