Grouping Counts - Can You Group WIDTH_BUCKET Counts?
978455Dec 6 2012 — edited Dec 7 2012Hi,
I'm using Oracle 11g.
I am trying to get a count of customers who have a certain count of orders. I have a parent to child relationship between customer (parent) and orders (child). The output should be something like:
0-100; 35 (35 customers have zero -100 orders)
101-200; 55 (55 customers have this many orders)
201-300; 12 (12 customers have this many orders)
I tried using the WIDTH_BUCKET function but it doesn't 'group' the counts, and I don't know how it works over the join between the parent/child tables.
Any help is GREATLY appreciated. My skill level in SQL is uh, well on the low end honestly.
I posted my crude attempts below
Thanks
Alex
~~~~~~
select rownum, width_bucket(order_count, 0, 2500, 11) from (
select c.customer_id, count(o.order_id) as order_count
from CUSTOMER c LEFT JOIN ORDERS o
using (customer_id)
where c.last_update_user = 'MDM_ETL' and
o.last_update_user = 'MDM_ETL'
group by c.customer_id
order by order_count desc)