Re: SQL: Counting occurances

On Wed, 04 Feb 1998 22:08:57 GMT, tbarry_at_uspe.com (Doug Bonson) wrote: Hi Doug,

didn't test but may be the solution at least might give you a hint:

SELECT c.cust_id, NVL(COUNT(o.order_id),0) from
Customer c,
Orders
where
c.cust_id=o.cust_id(+)
and
c.cust_type=<desired type>
;

>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