MJ Bonanno MJ Bonanno - 2 years ago 97
SQL Question

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

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download