Not getting correct results using distinct

Databases
1

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?

Thanks!

2

the query is telling you what data you have in those tables

if it isn’t what you expected, then either you’ve not filtered out the rows you want properly, or else there is duplication of some payments

3

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.

Thoughts r937 ?

4

because there are more payment rows in the table than you think there are

:cool:

5

LOL. Yes, apparently. But I’m weeding out very specific ones with this:

where type = ‘Gold’ and frequency = ‘Monthly’

Can you give me a clue?

6

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

7

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?

Thanks!

8

but the COUNT(DISTINCT uid) ~did~ work

it was the SUMamount) that was off

then the SUM(amount) for this particular uid was correct

but if the overall total is off, that means there are some extra rows in there

9

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?

10

sent you a PM