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

Solutions

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.

Similar questions

How to set condtion in shopping cart to apply discount only for product between two price ranges
I need a condition in magento shopping cart price rules where we can set discount for products between two price ranges . For example: I need to apply discount for product that are in between $100 and $200
How to group numeric attribute ranges similar to prices in layered navigation?
I have a numeric attribute that is a size (in cm). I would like to group products into ranges of the attribute similar to how prices are grouped. So if I have several products with sizes 151cm, 152 cm, 153cm it should group them into a "150cm - 155cm" option in layered navigation. What is the best way to go about doing this?
How to change the "SHOP BY Shopping options" price ranges when viewing a category on the front page?
When I click on any category - it shows the SHOP BY Shopping Options box on the left and gives several Price range options like: $0.00 - $99.99 (46) $100.00 and above (14) How can I customize it? I want to setup something like this: $0.00 - $50 $50 - $100 $100 and above Thanks for any suggestions! ;-)
Shipping Table rates Weight vs Destination Ranges
I have this CSV I have imported it into Store->Configuration->Sales->Shipping Methods->Table Rates successfully But when I buy an item weighing 60 kg and the shipping address is Sevilla, shows me the price of the first row (11,00 €) instead of the second (25,00 €) Can you help me, please?
How to Restrict magento admin panel access via particular IP addresses/ranges
I am newbie in Magento. I want to restrict Magento admin panel access via particular IP addresses/ranges. Please guide me.
Magento 2 Commerce Edition: how to manage promo date ranges?
We are on M2.3 Commerce Edition. I have some cart price rules that were working fine and then suddenly quit working. They remain active and all conditions are met. The only thing I can think of is that there is a date range that has expired. But we cannot see any date range in the admin panel, so we cannot confirm this. I've read through several me...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.