Solved

Oracle help with a counting query across tables

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

808 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