0

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.