SQL: SUM required for results that match the HAVING query

Question

I have a table where we write to custom values ​​such as money_spent, money_spent_on_candy and date.

So, the columns in this table (let it be called MoneyTable) will be:

• Userid
• Money_spent
• Money_Spent_On_Candy
• date

My goal is SUM total amount of money_spent - but only for those users where they spent more than 10% of their total money spent on a date range on sweets.

What will this request be?

I know how to select users who have this - and then I can output the data and summarize them manually, but I would like to do this in a single request.

I’d like a request to get the amount of Expense per user only for users who spent> 10% of their money on sweets.

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; 

Answer 1

You could not do this with a single request. You will need a query that can go back in time and retroactively filter the source table to process only users with 10% candy consumption. Fortunately, these subqueries are:

 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 internal query makes a difficult climb to find out what users spent “10%”, and then the external query uses the subquery as a virtual table to sum the Money_Spent amounts for each user.

Of course, this only works if you need ONLY the global amount of Money_Spent. If you also need an amount for each user, then it would be better for you to simply run an internal request and execute the total amount in your application.

Answer 2

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 

Answer 3

Or you can use a view:

 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;