Solved

Oracle help with a counting query across tables

Posted on 2011-09-14
4
251 Views
Last Modified: 2012-05-12
Hi All,

I need some help with doing a count based on counting the number of entries for a customer, call it table A, where the actual count is based on a customer id and condition in a table call it tableD, but the link between the customers is another table, call it tableC. The link between tableA and tableD for the data is a unique id.

So in the following example, the output would be:
Customer          Count
=======           =====
Coke                    2

(assuming fields in row1_id and row2_id met a condition)

tableA
row1>
id1
customer_name: Coke
customer_ref: Coke123
....

row2>
id2
customer_name: Coke
customer_ref: Coke123
....

tableC
row1>
customer_ref: Coke123
customer_id:XYZ

tableD
row1>
id1
field1
field2
customer_id:XYZ

row2>
id2
field1
field2
customer_id:XYZ

Below is what I tried to run which was taking too long and obviously not done the best way.

select a.customer_name, count(a.customer_name) from tableA a, tableC c, tableD d 
where a.id in (select id from tableD where field1 = '0')
group by a.customer_name

Open in new window

0
Comment
Question by:mgferg
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36536516
>>from tableA a, tableC c, tableD d

The where clause doesn't join the columns of all the tables.  This is generating a cartesian product.

Can you provide more data in a better format on how the tables join?
0
 
LVL 7

Accepted Solution

by:
Jacobfw earned 125 total points
ID: 36559771
select a.customer_name, count(*)
from tableA a, tableC c, tableD d
where a.customer_ref in c.customer_ref
and     c.customer_id = d.customer_id
and     d.field1 = '0'
group by a.customer_name
0
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36575821
Did this SQL statement help?  Are you still having problems with the proper SQL to use?
0
 

Author Closing Comment

by:mgferg
ID: 37268483
Apologies for delay in responding.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now