Solved

Oracle help with a counting query across tables

Posted on 2011-09-14
4
250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

914 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