Number of Customers Total Spend in the Past Year Output into £20 Ranges
I'm basically looking to build a query that'll return the number of customers in sales_flat_order who have spent in total year to date:
- Between £0 and £20
- Between £20.01 and £40
- Between £40.01 and £60
- etc, to the maximum total spend by a customer this year
This is what I came up with:
SELECT LowerBound, UpperBound, count(1) AS CustomersInBound FROM ( SELECT customer_id, cast(sum(subtotal_invoiced)/20 as unsigned) * 20 as LowerBound, (cast(sum(subtotal_invoiced)/20 as unsigned) + 1) * 20 as UpperBound FROM sales_flat_order AS sfo WHERE sfo.status = 'complete' AND created_at >= DATE_SUB(date('2016-12-05'),INTERVAL 1 YEAR) AND created_at <= date('2016-12-05') GROUP BY sfo.customer_id ) as T Group by LowerBound, UpperBound
The limitation is, if someone hasn't purchased between a boundary, this boundary will be omitted from the query output.
E.g. If no one's total value of purchases lies between £180 and £200, the Boundary £180-£200 will not be output with zero customers.