I have a query that gave me a list of store names and its respective amounts. However I would like to sum and categorize the amounts like this:
Store Name | $0-$50 | $50 – $100 | $100 – $500 | $500+
Based on the following screenshot:
Here is my condition:
If the TotalAmount for the store is between $0-$50, then it will be summed and go into $0-$50 bucket If the TotalAmount for the store is between $50-$100, then it will be summed and go into $50-$100 bucket If the TotalAmount for the store is between $100-$500, then it will be summed and go into $100-$500 bucket If the TotalAmount for the store is greater than $500, then it will be summed and go into $500+ bucket
Here is my basic query for the above snapshot
Select s.StoreName , r.TotalAmount --, SUM(r.TotalAmount) as TotalAmount FROM Store s join ShoppingList sl on s.StoreId = sl.StoreId join Purchase p on p.ShoppingListId = sl.ShoppingListId join Receipt r on r.PurchaseId = p.PurchaseId
Advertisement
Answer
You need conditional aggregation. Not clear what you want to do about values which are on the border, I will leave that to you:
Select s.StoreName , SUM(CASE WHEN r.TotalAmount >= 0 AND r.TotalAmount <= 50 THEN r.TotalAmount END) as [$0 - $50] , SUM(CASE WHEN r.TotalAmount > 50 AND r.TotalAmount <= 100 THEN r.TotalAmount END) as [$50 - $100] , SUM(CASE WHEN r.TotalAmount > 100 AND r.TotalAmount <= 500 THEN r.TotalAmount END) as [$100 - $500] , SUM(CASE WHEN r.TotalAmount > 500 THEN r.TotalAmount END) as [$500+] FROM Store s join ShoppingList sl on s.StoreId = sl.StoreId join Purchase p on p.ShoppingListId = sl.ShoppingListId join Receipt r on r.PurchaseId = p.PurchaseId GROUP BY s.StoreId, -- always use a unique column for aggregation s.StoreName