[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle help with a counting query across tables

Posted on 2011-09-14
4
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

649 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