MySQL - Group and total, but return all rows in each group

Question

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.

Answer 1

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.

Answer 2

SELECT member_id, amount
FROM sold_items si
INNER JOIN (SELECT member_id, 
SUM(amount) AS total 
FROM `sold_items` 
GROUP BY member_id 
HAVING total > 50) spenders USING (member_id)

The query you have already built can be used as a temporary table to join with. if member_id is not an index on the table, this will become slow with scale. The word spenders is a table alias, you can use any valid alias in its stead.