Creating Buckets For Sales Data

  • Hello all,

    Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.

    I want to take the Sales Order Total and then count how many times it falls into a specific bucket.

    I've included the script where I've left with sample data so you can run in into a tempdb.

    I hope to have the end result look like the below summary if you use my sample data.

    Count Less Than 5K: 5

    Count Greater Than 5K Less Than 10K: 0

    Count Greater Than 10K Less Than 25K: 3

    Count Greater Than 25K: 2

    Here is the script to set up the tempdb

    use tempdb

    go

    /*

    drop table #salesinvoice;

    drop table #salesinvoicedetail;

    */

    --create the temp tables

    create table #salesinvoice

    (

    OrderIdvarchar (50)not null

    ,OrderDate datetime

    )

    create table #salesinvoicedetail

    (

    OrderIdvarchar (50)not null

    , OrderLineintnot null

    , LineAmount intnot null

    , ProfitAmountintnot null

    )

    --insert test data

    insert into #salesinvoice

    (

    OrderID,OrderDate

    )

    Values

    ('SI201','02/02/2015')

    ,('SI202','02/03/2015')

    ,('SI203','02/03/2015')

    ,('SI204','02/04/2015')

    ,('SI205','02/04/2015')

    ,('SI206','02/04/2015')

    ,('SI207','02/04/2015')

    ,('SI208','02/05/2015')

    ,('SI209','02/05/2015')

    ,('SI210','02/06/2015')

    insert into #salesinvoicedetail

    (

    OrderID,OrderLine,LineAmount,ProfitAmount

    )

    Values

    ('SI201','1','500','100')

    ,('SI201','2','400','50')

    ,('SI201','1','6000','200')

    ,('SI201','2','8000','1000')

    ,('SI201','3','11000','3000')

    ,('SI201','4','15000','3000')

    ,('SI201','5','550','50')

    ,('SI202','1','5500','1050')

    ,('SI202','2','6100','800')

    ,('SI203','1','510','120')

    ,('SI203','2','700','200')

    ,('SI204','1','15000','2000')

    ,('SI204','2','1001','205')

    ,('SI205','1','2750','350')

    ,('SI206','1','12500','2500')

    ,('SI207','1','25450','2349')

    ,('SI208','1','4500','480')

    ,('SI209','1','3850','320')

    ,('SI210','1','2575','391')

    --review data

    SELECT * FROM #salesinvoicedetail

    SELECT * FROM #salesinvoice

    Below is my query where I hope you can help me change it to perform a count on the summarized totals.

    select

    si.OrderID as OrderNum

    ,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K

    ,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k

    ,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k

    ,sum(case when sid.LineAmount >=25000 then 1 else 0 end) as GT25k

    ,max(OrderLine) as LineCount

    From #salesinvoice as si

    inner join #salesinvoicedetail as sid

    on sid.OrderID = si.OrderID

    group by rollup (si.OrderID)

    Order by OrderNum

    /*****

    Here are the order totals.

    SI201 = 41450

    SI202 = 11600

    SI203 = 1210

    SI204 = 16001

    SI205 = 2750

    SI206 = 12500

    SI207 = 25450

    SI208 = 4500

    SI209 = 3850

    SI210 = 2575

    And the desired result would tell me the below summary. The final query can be column based. I just write this in rows for clarity.

    Count Less Than 5K: 5

    Count Greater Than 5K Less Than 10K: 0

    Count Greater Than 10K Less Than 25K: 3

    Count Greater Than 25K: 2

    ******/

    Thank you in advance for your help.

  • an idea

    SELECT

    sum(case when sumla < 5000 then 1 else 0 end) as LT5K

    ,sum(case when sumla >=5000 and sumla < 10000 then 1 else 0 end) as GT5kLT10k

    ,sum(case when sumla >=10000 and sumla < 25000 then 1 else 0 end) as GT10kLT25k

    ,sum(case when sumla >=25000 then 1 else 0 end) as GT25k

    FROM (

    SELECT si.OrderId as sumoi, SUM(sid.LineAmount) AS sumla

    FROM #salesinvoice AS si INNER JOIN

    #salesinvoicedetail AS sid ON si.OrderId = sid.OrderId

    GROUP BY si.OrderId) x

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Another option, just in case that you want to have your information returned in a different way.

    WITH Groups(GroupID, GroupDesc) AS(

    SELECT 1, 'Count Less Than 5K' UNION ALL

    SELECT 2, 'Count Greater Than 5K Less Than 10K' UNION ALL

    SELECT 3, 'Count Greater Than 10K Less Than 25K' UNION ALL

    SELECT 4, 'Count Greater Than 25K'

    ),

    Summary AS(

    SELECT CASE

    WHEN SUM(sid.LineAmount) < 5000 THEN 1

    WHEN SUM(sid.LineAmount) < 10000 THEN 2

    WHEN SUM(sid.LineAmount) < 25000 THEN 3

    ELSE 4 END GroupID

    FROM #salesinvoice si INNER JOIN

    #salesinvoicedetail sid ON si.OrderId = sid.OrderId

    GROUP BY si.OrderId

    )

    SELECT g.GroupDesc,

    COUNT( s.GroupID)

    FROM Groups g

    LEFT

    JOIN Summary s ON s.GroupID = g.GroupID

    GROUP BY g.GroupDesc

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • J Livingston's query runs faster. Both solutions work well.

    Sorry for the late reply but it really helped me in crunch.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply