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.
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.