[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Oracle help with a counting query across tables

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
mgferg
Asked:
mgferg
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>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
 
JacobfwCommented:
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
 
JacobfwCommented:
Did this SQL statement help?  Are you still having problems with the proper SQL to use?
0
 
mgfergAuthor Commented:
Apologies for delay in responding.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now