CODEWITHSUNDEEP
mysql
Geesu
Geesu

Reputation: 6008

MySQL - SELECT IN - Counting the # of rows for a user_id in a result?

I'm basically trying to query a table to see who might be gaming my paypal system.

The idea is I query my table to see what payments have failed, and then total up the number of failures by user_id. Pretty straightforward concept, I'm just really struggling on how to do it.

My start query:

SELECT * FROM `paypal_ipn` WHERE initial_payment_status =  'Failed'

I then want to use the above query, and just print out a total per payer_id (a column).

Is this possible in MySQL alone or do I need to use a PHP script?

Upvotes: 1

Views: 97

Answers (4)

Asaph
Asaph

Reputation: 162781

You basically want to do a sql GROUP BY operation. I couldn't tell from the question whether the data needs to be grouped by a column called user_id or payer_id. One of these queries should work for you:

SELECT user_id, count(*) FROM `paypal_ipn`
WHERE initial_payment_status = 'Failed'
GROUP BY user_id;

SELECT payer_id, count(*) FROM `paypal_ipn`
WHERE initial_payment_status = 'Failed'
GROUP BY payer_id;

Upvotes: 3

LSerni
LSerni

Reputation: 57388

You can do something like this; I've added the total of failed payments to show you other interesting things you can query (I don't know if the payment is really called payment, mind).

SELECT payer_id,
    COUNT(*) AS transactions,
    SUM(payment) AS total
FROM `paypal_ipn` WHERE initial_payment_status = 'Failed'
GROUP BY payer_id;

Upvotes: 0

Unpredictable
Unpredictable

Reputation: 113

You just need to group by on user_id. So the query would be look like:

SELECT user_id, count(*) FROM paypal_ipn WHERE initial_payment_status = 'Failed' GROUP BY user_id;.

Upvotes: 0

Frank Conry
Frank Conry

Reputation: 2718

How about this:

Select payer_id, count(paypal_tranaction_id) from paypal_ipn where initial_payment_status = 'Failed' group by payer_id

Upvotes: 0

Related Questions