Histogram: Counting orders with variable bins in SQL -

i have table containing orders, items, , prices. trying generate histograms each item based on prices.

create table #customer_pricing ( customer_id int, item_id varchar(10), qty decimal(5,2), price decimal(5,2), ) ; go  -- insert statements insert #customer_pricing values(128456, 'som 555', 8, 2.50) insert #customer_pricing values(123856, 'som 554', 1, 2.50) insert #customer_pricing values(123456, 'som 554', 55, 2.00) insert #customer_pricing values(123556, 'som 555', 2, 2.20) insert #customer_pricing values(123456, 'som 553', 12, 2.13) ; 

for each item, wanted 3 bins determined bin sizes dividing difference of max-min 3, adding value min.

with stats_table_cte (item_id2,max_p, min_p, int_p, r1_upper, r2_lower, r2_upper, r3_lower) (   select  item_id             ,max(price)              ,min(price)             ,(max(price) - min(price))/3             ,min(price)+(max(price) - min(price))/3-0.01             ,min(price)+(max(price) - min(price))/3                      ,min(price)+((max(price) - min(price))/3)*2-0.01             ,min(price)+((max(price) - min(price))/3)*2                                                      #customer_pricing         group item_id) 

now, need count frequencies each range , each item. have attempted using sum(case...) unsuccessful.

select item_id     ,sum(case when price <= r1_upper, 1 else 0 end) r1_count     ,sum(case when price >= r2_lower , <= r2_upper, 1 else 0 end) r2_count     ,sum(case when price >= r3_lower, 1 else 0 end) r3_count stats_table_cte group item_id 

i attempted use count in form select item_id, price count(price <= r1_upper) r1_count.... got stuck

in 1 attempt, inner joined #customer_pricing table , stats_table_cte didn't know go there.

ideally, output table appear follows: *this not actual data, included show desired format of output. item id min_p r1_upper (r2 bins) r3_lower max_p r1_count r2_ct som 553 2.00 2.16 saving space 2.33 2.50 2 1 som 554 2.13 2.48 2.88 3.25 1 0 som 555 2.31 2.51 2.72 2.92 3 2

*the format of output table off, have item id, bins, , counts across top grouped item

here recommendation:

with stats_table_cte (     select  item_id, max(price) maxprice, min(price) minprice,             (max(price) - min(price))/3 binsize     #customer_pricing     group item_id    ) select cp.item_id,      sum(case when price < minprice + binsize 1 else 0          end) r1_count      sum(case when price >= minprice + binsize , price < minprice+ 2*binsize               1 else 0          end) r2_count      sum(case when price >= minprice + 2*binsize               1 else 0          end) r3_count #customer_pricing cp join      stats_table_cte st      on st.item_id = cp.item_id group cp.item_id 

the important part join #customer_pricing. important simplification of logic -- can define bounds bins , use <, rather having lower , upper bound each one. also, query had syntax errors in it.

note in many databases, cte not necessary because use window functions. question not tagged database (although guess is), change seems unwarranted.