Re: SQL: Counting occurances

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/05
Message-ID: <34d974d9.2221329@www.sigov.si>#1/1

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

>....[SNIP]....
>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)

Use the outer join!

SQL> select a.cust_id, count(b.order_id)   2 from Customer a, Orders b
  3 where a.cust_type=1
  4 and a.cust_id = b.cust_id(+)
  5 group by a.cust_id;

  CUST_ID COUNT(B.ORDER_ID)

--------- -----------------
        1                 3
        3                 1
        4                 0

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Feb 05 1998 - 00:00:00 CST