SQL: Need to SUM on results that meet a HAVING statement

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 开发者_Python百科> 0.1;

You couldn't do this with a single query. You'd need a query that could reach back in time and retroactively filter the source table to handle only users with 10% candy spending. Luckily, that's kind of what sub-queries do:

SELECT SUM(spent) FROM (
   SELECT SUM(Money_Spent) AS spent
   FROM MoneyTable
   WHERE (DATE >= '2010-01-01')
   GROUP BY UserID
   HAVING (SUM(Money_Spent_On_Candy)/SUM(Money_Spent)) > 0.1
);

The inner query does the heavy lifting of figuring out what the "10%" users spent, and then the outer query uses the sub-query as a virtual table to sum up the per-user Money_Spent sums.

Of course, this only works if you need ONLY the global total Money_Spent. If you end up needing the per-user sums as well, then you'd be better off just running the inner query and doing the global total in your application.

You can use common table expressions. Like this:

WITH temp AS (SELECT 
    UserId, 
    SUM(Money_Spent) AS MoneySpent, 
    SUM(Money_Spent_On_Candy)/SUM(Money_Spent) AS PercentCandySpend 
FROM MoneyTable 
WHERE DATE >= '2010-01-01' 
HAVING PercentCandySpend > 0.1)
SELECT
     UserId
     SUM(MoneySpent)
FROM UserId

Or you can use a derived table:

SELECT SUM(Total_Money_Spent)
FROM (    SELECT UserId, Total_Money_Spent = SUM(Money_Spent), SUM(Money_Spent_On_Candy)/SUM(Money_Spent) AS PercentCandySpend
          FROM MoneyTable
          WHERE DATE >= '2010-01-01'
          HAVING PercentCandySpend > 0.1 ) x;