Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 30th, 2006, 03:59 PM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need Help - Nested/Subselect SQL

All,
would truly appriciate any help.

I need the out put of count(a) column to divide by table2.columnD

select count(a)
from table1
where
group by a

from this output i need to then divide it by table2.columnD

what im looking for is the result of count(a)/table2.columnD

thanks in advance

 
Old October 30th, 2006, 09:38 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

taylor99ss:
I need a clarification of your issue. Are you saying the following:
  There is only one field or column in table1, which is a?
  You need to count the a-column for total records in table1, correct:
  You then need to , for each record field columnD of table2, divide this field into totalrecords (column-a), as long as columnD is not = 0.
  If columnD=0, then what do you want to do?

This is accomplished in an anonymous block, correct?

Let me know......
 
Old October 31st, 2006, 08:45 AM
Registered User
 
Join Date: Oct 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

sorry let me try this again.
I know this exact code wont run, i tried to replace the exact table and column names. sorry if its confusing

select count(table1.column1), table3.column1
from table 1, table3
where table1.column3 = table3.colunm3
and date = 123456
and table1.column1 = 1
group by table3.column1

AND THE SECOND

select count(table2.column1), table3.column1
from table 2, table3
where table2.column3 = table3.colunm3
and date = 123456
group by table3.column1

they are similar. but different. I need to figure out a way to get the count(table1.column1), count(table2.column1) and group by table3.column3 all in the same select.

thanks

 
Old November 1st, 2006, 04:48 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

Taylor99ss:
The following code is based solely on your most recent response:
SELECT (Count(tbl1.col1)/Count(tbl2.col1)), tbl3.col1, tbl3.col3
FROM tbl1, tbl2, tbl3
WHERE date=123456 and tbl1.col1= 1 and
      tbl1.col3 = tbl3.col3 and
      tbl3.col3 = (SELECT tbl2.col3, tbl3.col3 FROM tbl2,tbl3 WHERE date=123456)
GROUP BY tbl3.col1

Warning: The above code has not been tested. However, in the first SELECT statement the count of tbl1 will be of those records where tbl.col1 = 1 in addition to the rest of the selection criteria (WHERE-clause). The count of tbl2 will be of all the records in tbl2 regardless of the selection criteria. IF you want to count the records that meet the selection criteria then in the FIRST select statement change count(tbl2.col1) to count(tbl2.col3).

Notice in your code the WHERE-clauses, with particular attention to tbl1.col3 = tbl3.col3 and in the second select statement tbl2.col3 = tbl3.col3. In order to create the subquery or nested query, I applied the math property: if a = b, b = c, then a = c. So the decision is left up to you as to whatyou want to count in tbl2, i.e. all records or just those that meet the selection criteria.

Hope this helps.....
 
Old November 1st, 2006, 05:03 AM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

Ooops: ****** W A R N I N G ********

I made a slight error in the SELECT statement of the SubQuery: It should read:
tbl3.col3 = (SELECT tbl2.col3 FROM tbl2 WHERE date=123456) My original thought was that tbl3.col1 was needed by GROUP BY statement, but it is handle by the first SELECT statement.

Feel Better now.....




Similar Threads
Thread Thread Starter Forum Replies Last Post
Write Nested XML document into SQL Server tables boondocksaint20 VB.NET 0 May 2nd, 2006 12:35 PM
how to write sql statement for nested subuery thas123 SQL Server 2000 3 February 23rd, 2006 02:37 PM
Plz Help - Nested SQL Statements? esemerda SQL Language 2 February 28th, 2005 06:41 AM
Simple subselect & image link ymoisan Excel VBA 0 May 4th, 2004 10:04 AM
Subselect? pokermagic SQL Language 3 December 11th, 2003 02:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.