# 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.