Skip to content
Advertisement

Categorize the total amounts

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:

enter image description here

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement