Tricky MySQL query statement help needed

[flashdeveloper]

I'm trying to write a php script to handle a complex query and I just can't get the syntax right.

In table "Payments" there are 10,000 records lets say, and the key is a unique "transactionID". The other relevant columns are "user_name" and "payment_amount". The user name is not unique, and each time a user makes a payment, a new record is inserted into to the table with the same user name.

Heres what I need to know. For each user who has a "payment_amount" of 4.99, return each record where that "user_name" appears more than once in the table. In other words, I know the number of users that paid 4.99, but I want to know how many of them paid again.

Thanks goes out to anyone who finds this stuff interesting enough to take a stab at it.

[MarkB423]

Try this for your SQL:

"SELECT transactionID, user_name, payment_amount, COUNT() AS num_transactions
FROM Payments
WHERE payment_amount = 4.99
GROUP BY payment_amount, user_name, transactionID
HAVING COUNT() > 1
ORDER BY num_transactions"

Let me know what happens...

edited: switched user_name with transactionID in the GROUP BY clause...

[tomhath]

If I understand the requirement the query should look something like this:

select user_name, payment_amount, count() as numpayments
from Payments
group by user_name, payment_amount
having count() > 1

[tomhath]

Hey Mark, great minds must think alike :rolleyes:

[MarkB423]

haha, they must...

Tom, maybe you can answer a question I've been wondering about the GROUP BY clause...

I read that you have to have every column that is in the SELECT statement in the GROUP BY statement, except for aggregate functions (such as COUNT())... would that mean that in the SQL statement I wrote above, that I could not omit transactionID, for instance, from the GROUP BY clause?

[tomhath]

It depends on the DBMS whether or not every selected field must be in the group by, although it doesn't make sense to me not to have them all there (what value would it select if there are several in the group?). I think mysql let's you be sloppy, I know MS SQLServer enforces every non-aggregate field selected must be in the group by.

By the way, your query shouldn't have the transactionid at all. It's the unique key so you can't group anything by it.

[flashdeveloper]

I tried that query but it produced no records, and I am sure that there exist multiple records from the same user_name, where one of the payments was 4.99

The other payments may be some other value like 9.99, or 14.99

I am using MySQL 3.23.10-alpha and PHP 4.0.3pl1

[MarkB423]

Try this, flash...

"SELECT transactionID, user_name, payment_amount, COUNT() AS num_transactions
FROM Payments
GROUP BY user_name
HAVING COUNT() > 1
ORDER BY num_transactions"

Let me know if this works now...

[flashdeveloper]

Thanks Mark,

I modifiied it slightly, since I really just want to look at the groups that contain 4.99 payments specifically. Since I could not isolate these with the query, it helps to have them at the top of the list.

SELECT transactionID, user_name, payment_amount, COUNT() AS num_transactions
FROM Payments
GROUP BY user_name
HAVING COUNT() > 1
ORDER BY payment_amount

My only concern is whether or not this method finds the 4.99 in every group, or if the ORDER BY simply uses the first payment_amount it finds for that particular user_name. If the later is true, then the 4.99 rows that appear at the beginning of the query result, do not represent all of the records that may have multiple payments including one for 4.99. It would only show the records where 4.99 was the first payment it found for a particular user that had multiple payments.

I hope that doesn't sound too tortured of an explanation, but if you understand what I mean, you will see the dilemna. It all comes down to the way MySQL behaves when creating ordered groups, right?

[MarkB423]

I see your problem... I can't think of anything off the top of my head... let me know if you think of a resolution before I do...

[tomhath]

Don't know if this will work on mysql, but it does on SQLServer:

select a.user_name, count(distinct a.transactionID)
from Payments a
left join Payments b
on a.user_name = b.user_name
and b.payment_amount = 4.99
where b.user_name is not null
group by a.user_name
having count(distinct a.transactionID) > 1

[[deleted]]

Don't you just love MySQL for it's lack of subqueries? 😉

[flashdeveloper]

Finally figured it out, and all it took was a simple tweak of Mark's query:

SELECT transactionID, user_name, payment_amount, COUNT() AS num_transactions
FROM Payments
GROUP BY user_name
HAVING COUNT() > 1 AND payment_amount = 4.99
ORDER BY payment_amount

This will search the payment table looking for users who payed 4.99 once, and later payed again. This query excludes all records where a user paid 4.99, but never paid again. It also excludes all records where a user payed just once. Lastly it excludes records where a user paid some other price like 9.99 and later paid 14.99, but never paid 4.99

You can ORDER BY any way you like this way also.

A note to users who like to test thier queries in PHPMyAdmin 2.2.0, you will need to add a LIMIT statement and make it something larger than your expected result set. Queries like this are too complex for PHPMyAdmin, though it is an outstanding tool.

[[deleted]]

You're not quite there yet I'm affraid:

Remember that SELECTing non-aggregates without including them in the GROUP BY means you will get semi-random results, you cannot trust this to be what you think it is.

You are selecting the transaction id while grouping on user-name, which means mysql will give you one transaction id, while the "HAVING COUNT(*) > 1" clearly indicates that there are more than one transacions, and thus more than one transaction_id's

Also note that the HAVING is done AFTER the GROUPING, which means it will only fire if the payment_amount equals 4.99, but again due to the GROUP BY, it's up to MySQL to decide wether to return 4.99 of any of the other prices (remember the count(*) says there must be more than one transaction, thus more than one price)

[flashdeveloper]

I noticed that I could produce two seperate results, simply by identifying my users by 'email_address' instead of 'user_name'.

I thought it was weird that 31 user_names met the criteria, but 43 email_addresses met the criteria also. Of course if any users had multiple email addresses, this would make sense, but that does not appear to be the case.

So I'm stumpped, what needs to be done to get an accurate result?

[MarkB423]

Okay, you can handle this by selecting all of the table data that is needed, reading it into an array, then using a foreach statement to remove all elements that do not have at least two elements (you'll need a multi-dimensional array), then using a foreach statement to print the array.

The array will be an array (index can be auto-assigned) with a value that is an array (with the index as the user id or something unique), with the payment values as an array (index is auto-assigned). Then you can either create an array of variables (index would be the user ID) that are either true or false that are marked TRUE when a value for 4.99 is found, so you know whether to display that user's transactions or not.

I am 99.9% certain that I could program PHP to handle this situation... which seems to be the only option since we cannot get MySQL to do it....

If my description above confuses you or you cannot get it to work, I could program it for you for a very small fee... Let me know if you find a solution or what you decide...

[MarkB423]

What if you tried this in the SQL statement...

"SELECT DISTINCT a.transactionID, a.user_name, a.payment_amount
FROM Payments AS a, Payments AS b
WHERE a.user_name = b.user_name
AND a.transactionID != b.transactionID
ORDER BY a.payment_amount"

This should return all transactions where the user had more than one transaction... try it out and let me know what the deal is with this... it is a long shot, but it beats using PHP as the means of sorting the data...

[tomhath]

What happens with the LEFT JOIN I posted? That should work (as Vincente pointed out it would've been a lot clearer with a subselect, but that's not an option).

[MarkB423]

Well, Tom... he may have to select more than the user_name from this query, such as the transactionID or payment_amount, and if he does that, as Vincente pointed out, he must also group by those columns. So, when he does this, the COUNT aggregate will only be applied to the last-grouped field, so no matter what way you cut it, it is not going to give him the result he needs.

The only time you don't have to also group a column that you are selecting is if it is an aggregate function. Keep in mind these things as well:

• You may not group by a column that you did not select
• You may not group by an alias, you may only group by the full field name or the aggregrate function's syntax
• The HAVING clause will only be applied to the last-grouped column

[MarkB423]

This statement has some modifications that will return only those users where the user has multiple transactions AND one of those transactions is 4.99...

"SELECT DISTINCT a.user_name, a.user_ID
FROM Payments AS a, Payments AS b
WHERE a.user_name = b.user_name
AND a.transactionID != b.transactionID
AND a.payment_amount = 4.99
ORDER BY a.user_name"