Re: SQL: Counting occurances

Hi Doug

Try this one:

Select c.custid,sum(decode(o.order_id,null,0,1)) from customer c, orders o
where c.cust_id = o.cust_id(+)
and c.cust_type = 1
group by c.cust_id;

regards,

Michael Ringbo, mri_at_dde.dk

Doug Bonson wrote:

Given the following two tables:

create table Customer
(cust_id number(6),

   cust_type number(1))

1, 1
2, 2
3, 1
4, 1
5, 2

create table Orders
(order_id number(7),

   cust_id number(6))

1, 1
2, 2
3, 3
4, 1
5, 5
6, 2
7, 1

How can I get a list of all customers of type 1 with their respective number of orders? Note that some customers may have no orders.

Result set (for type = 1) should be:

1, 3 (customer 1 has 3 orders)
3, 1 (customer 3 has 1 order)
4, 0 (customer 4 has 0 orders)

Thanks,
Doug Received on Thu Feb 05 1998 - 00:00:00 CST