CODEWITHSUNDEEP
sqlgroup-bybucket
user15693947
user15693947

Reputation: 11

SQL - I need a query to bucket orders by customers into groupings of order sizes 1 to 10 by order day. Each invoice can have 1 to 10 orders on it

I want to run a query that will count the number items invoiced on a given invoice number on a given day by customer id. Each invoice can have up to a total of 10 items invoiced on it. I want to know what the spread is. How many times is the full count of 10 items included on an invoice order, and then subsequent totals for how many times there are only 9, 8, 7, 6, 5, 4, 3, 2, and 1 items on an individual invoice for a given customer.

My data looks like this (Customer Invoice Table)

Order Date MMDDYYYY | Customer Id | Invc Num  | Item Sku 
 04/01/2021          1                 01      11111

 04/01/2021          2                 02      11112
 04/01/2021          2                 03      11113
 04/01/2021          2                 04      11111

 04/02/2021          3                 05      11112
 04/02/2021          3                 05      11113
 04/02/2021          3                 05      11114
 04/02/2021          3                 06      11115

 04/03/2021          3                 07      11117
 04/03/2021          3                 07      11118

 04/03/2021          4                 08      11114
 04/03/2021          4                 09      11120
 04/03/2021          4                 09      11121
 04/03/2021          4                 09      11122
 04/03/2021          4                 09      11123
 04/03/2021          4                 09      11124
 04/03/2021          4                 09      11125
 04/03/2021          4                 09      11126
 04/03/2021          4                 09      11127
 04/03/2021          4                 09      11128
 04/03/2021          4                 09      11129

Expected results:

Order Date MMDDYYYY | Customer Id | Bucket 1 | Bucket 2 | Bucket 3 | Bucket 4 | Bucket 5 | Bucket 6 | Bucket 7 | Bucket 8 | Bucket 9 | Bucket 10 | Total Items Ordered
     04/01/2021     |      1      |    1     |     0    |    0     |    0     |    0     |    0     |    0     |    0     |    0     |    0      |       1
     04/01/2021     |      2      |    3     |     0    |    0     |    0     |    0     |    0     |    0     |    0     |    0     |    0      |       3
     04/02/2021     |      3      |    1     |     0    |    1     |    0     |    0     |    0     |    0     |    0     |    0     |    0      |       4
     04/03/2021     |      3      |    0     |     1    |    0     |    0     |    0     |    0     |    0     |    0     |    0     |    0      |       2
     04/03/2021     |      4      |    1     |     0    |    0     |    0     |    0     |    0     |    0     |    0     |    0     |    10     |      11

My SQL so far looks like this: Select order_date, customer_id, invc_num, count(invc_num) From customer_invoice_table group by order_date, customer_id, invc_num

Of course this only gives me the Total Items Ordered. I cannot figure out how to get the individual buckets. I can do it manually in a spreadsheet by counting the number of times an individual invc_num appears for the same customer on a given day, but I cannot figure out how to formulate this into an sql statement to give me the total orders grouped in each of the buckets.

Any thoughts on this?

Upvotes: 1

Views: 702

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If I understand correctly, you want the count of the number of times that each invoice appears per customer per day. For that, you can use two levels of aggregation. Then you want that count pivoted across "bucket" columns

select order_date, customer_id, sum(cnt) as num_invoices,
       sum(case when cnt = 1 then 1 else 0 end) as bucket_1,
       sum(case when cnt = 2 then 1 else 0 end) as bucket_2,
       sum(case when cnt = 3 then 1 else 0 end) as bucket_3,
       . . .
from (select order_date, customer_id, invc_num, count(*) as cnt
      from customer_invoice_table
      group by order_date, customer_id, invc_num
     ) t
group by order_date, customer_id

Upvotes: 1

Related Questions