Problem solve Get help with specific problems with your technologies, process and projects.

Using count_distinct on master and detail tables

We have a master and detail table. Can you perform a count_distinct on a column on a master table as well as count_distinct on a detail table?

I'm not exactly sure what you're wanting to do but see if the following does the trick:

SELECT count(distinct o.order_date), count( distinct oi.item_no)
  FROM order_item oi, orders o
 WHERE o.ord_no = oi.ord_no;
In this example, the master table is the ORDERS table and the detail table is ORDER_ITEM. The query selects the distinct count of the order_date column from ORDERS and the distinct count of the item_no column from ORDER_ITEM. The tables are joined on ord_no.

This should work for any non-key column just fine. But if you want to do distinct counts of key columns, ord_no for example, you need to change the query just a bit as follows:

SELECT count(distinct o.ord_no), count( distinct oi.ord_no)
  FROM order_item oi, orders o
 WHERE o.ord_no = oi.ord_no(+) ;
The difference here is the use of the outer join. This way, you would basically always get a valid row count for the ORDERS table even if an order did not have a related detail record (i.e. no items added to the order yet). For example, if the ORDERS table had 10 order records but the ORDER_ITEM table only had items for 7 of the 10 orders, the above statement would return 10 and 7. If you had not used outer join syntax, you would have gotten counts of 7 and 7.

I hope this helps!

For More Information

  • What do you think about this answer? E-mail the editors at editor@searchDatabase.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in May 2002

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.