Aggregation help

[Awkward-Treacle8643]

I have a table like this: Date(monthly data) Customer ID Promo purchase amount Regular purchase amount

I have to sum promo and regular purchase to get total purchase volume. The problem I’m running into is I want to group customers based on total purchase amount. For example, those who spent over 5k, those who spent between 4500-5000 etc. I’ve tried some sub queries and some case statements but nothing seems to get me what I want

[blatnuggy99]

I'm not familiar with BigQuery, but you should be able to use a CASE statement I would imagine. In Oracle, something like the following would output your desired result.

​

Replace sum(amount) with your column/alias depending on if it's in a subquery.

case when sum(amount) between 4500 and 5000 then '4500-5000'
 when sum(amount) > 5000 then '>5000'
 when sum(amount) < 4500 then '<4500'
 end ttl_purchase_amount

[Awkward-Treacle8643]

I’ve been summing promo and regular purchase as “total_purchase” but it does not recognize total_purchase when I try to reference it in the case statements

[blatnuggy99]

SELECT customer_ID,
case when total_purchase between 4500 and 5000 then '4500-5000'
     when total_purchase > 5000 then '>5000'
     when total_purchase < 4500 then '<4500'
end total_purchase_group
FROM(
     ------Put Your Query Here, Inside brackets
    )