CODEWITHSUNDEEP
phpmysqldatabasetransactions
user2475770
user2475770

Reputation: 27

Count transaction value for each user id

I have a database that is used for recording transactions by customers.

The table (transactions) is set out as follows: 

**ID | Added-date | userid | amount | department | transaction-date | addedby**

 1     yyyy-mm-dd   P1001    9.78     dpt 1        yyyy-mm-dd         username

 1     yyyy-mm-dd   P1023    19.78    dpt 2        yyyy-mm-dd         username

 1     yyyy-mm-dd   P1021    39.78    dpt 3        yyyy-mm-dd         username

 1     yyyy-mm-dd   T1501    9.78     dpt 2        yyyy-mm-dd         username

=======

What I would like to do is add up the total value of transactions for each user and be able to display the top 50 spenders.

Can anybody suggest a way to do this as I've drawn a blank so far.

Many thanks.

Upvotes: 0

Views: 198

Answers (3)

Bart
Bart

Reputation: 1018

SELECT userid, SUM(amount) AS total FROM transactions GROUP BY userid

Having an aggregate function like SUM in your SELECT clause would normally cause only one row to be outputted containing the total from all rows matched by the WHERE clause. GROUP BY is a modifier for these functions and makes them aggregate per unique value in the specified fields.

See: Group By

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26363

For the top 50 spenders:

SELECT userid, SUM(amount) AS TotalSpent
FROM transactions
GROUP BY userid
ORDER BY TotalSpent LIMIT 50

Upvotes: 0

Halcyon
Halcyon

Reputation: 57721

Did you try a SUM and GROUP BY?

Something like:

SELECT SUM(amount) as `total_amount`, userid
FROM `transactions`
GROUP BY `userid`

Upvotes: 1

Related Questions