SQL: Need to SUM on results that meet a HAVING statement
- by Wasauce
I have a table where we record per user values like money_spent, money_spent_on_candy and the date.
So the columns in this table (let's call it MoneyTable) would be:
UserId
Money_Spent
Money_Spent_On_Candy
Date
My goal is to SUM the total amount of money_spent -- but only for those users where they have spent more than 10% of their total money spent for the date range on candy.
What would that query be?
I know how to select the Users that have this -- and then I can output the data and sum that by hand but I would like to do this in one single query.
Here would be the query to pull the sum of Spend per user for only the users that have spent 10% of their money on candy.
SELECT
UserId,
SUM(Money_Spent),
SUM(Money_Spent_On_Candy) / SUM(Money_Spent) AS PercentCandySpend
FROM MoneyTable
WHERE DATE >= '2010-01-01'
HAVING PercentCandySpend > 0.1;
