CODEWITHSUNDEEP
phpmysqlsql
Max Troll
Max Troll

Reputation: 13

How to select all entries of a user from a database?

I am trying to make a "top purchaser" module on my store and I am a bit confused about the MySQL query.

I have a table with all transactions and I need to select the person (which could have one or many transactions) with the highest amount of money spent in the past month.

What I have:

name | money spent
------------------
john | 50
mike | 12
john | 10
jane | 504
carl | 99
jane | 12
jane | 1

What I want to see:

With a query, I need to see:

name | money spent last month
-----------------------------
jane | 517
carl | 99
john | 60
mike | 12

How do I do that?

I do not really seem to find many good solutions since my MySQL query skills are quite basic. I thought of making a table in which money is added to the user when he buys something.

Upvotes: 0

Views: 247

Answers (1)

GMB
GMB

Reputation: 222482

That's a simple aggregated query :

SELECT t.name, SUM(t.moneyspent) money_spent_last_month
FROM mytable t
GROUP BY t.name
ORDER BY t.money_spent_last_month DESC
LIMIT 1

The query sums the total money sped by customer name. The results are ordered by descending total money spent, and only the first row is retained.

If you are looking to filter data over last month, you need a column in the table that keeps track of the transaction date, say transaction_date, and then you can just add a WHERE clause to the query, like :

SELECT t.name, SUM(t.moneyspent) money_spent_last_month
FROM mytable t
WHERE 
    t.transaction_date >= 
        DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
    AND t.transaction_date <= 
        DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY t.name
ORDER BY t.money_spent_last_month DESC
LIMIT 1

This method is usually more efficient than using DATE_FORMAT to format dates as string and compare the results.

Upvotes: 2

Related Questions