I'm trying to write a query that finds each time the same person occurs in my table between a specific date range. It then groups this person and totals their spending for a specific range. If their spending habits are greater than X amount, then return each and every row for this person between date range specified. Not just the grouped total amount. This is what I have so far:

SELECT member_id,

SUM(amount) AS total

FROM `sold_items`

GROUP BY member_id

HAVING total > 50

This is retrieving the correct total and returning members spending over $50, but not each and every row. Just the total for each member and their grand total. I'm currently querying the whole table, I didn't add in the date ranges yet.

解决方案

JOIN this subquery with the original table:

SELECT si1.*

FROM sold_items AS si1

JOIN (SELECT member_id

FROM sold_items

GROUP BY member_id

HAVING SUM(amount) > 50) AS si2

ON si1.member_id = si2.member_id

The general rule is that the subquery groups by the same column(s) that it's selecting, and then you join that with the original query using the same columns.