CODEWITHSUNDEEP
sqlsql-serverhistogram
0nir
0nir

Reputation: 1355

SQL Histogram: Greater than 10 orders group as "10+ bucket"

Question is: Create a sql query to provide a histogram of the number of x people who made y number of orders. Anyone with > 10 orders should be grouped into a “10+” bucket

Step 1: I have created a 'temptable' as this:

Customerid    Order_Count
----------    -----------
CENTC         1
GROSR         2
LAZYK         2
LAUGB         3
NORTS         3
FRANR         3

Step 2: I tried the floor function for creating the histogram buckets but cannot get to bucket '10+' using the below syntax

select bucket, count(*) from
(select floor(order_count/10.00)*10 as bucket from TempTable
) t group by bucket

Kindly suggest of other suitable approaches. Thanks!

Upvotes: 2

Views: 395

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You would do case expressions to define the buckets and then aggregation for the final summary:

select (case when order_count >= 10 then '10+' else CustomerId
        end) as CustomerId,
       sum(Order_Count) as Order_Count
from temptable
group by (case when order_count >= 10 then '10+' else CustomerId
          end);

Upvotes: 2

Related Questions