Number of Customers Total Spend in the Past Year Output into £20 Ranges

Question

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

Answer 1

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.