Home » SQL & PL/SQL » SQL & PL/SQL » Counting in a subquery
Counting in a subquery [message #210420] Wed, 20 December 2006 14:26 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
Hey All,

I looked around and did not find much on what i am trying to do.

I have 3 tables

Accounts
Charges
Statements

I want to pull back a list of accounts and know the number of charges and statemetns each have in the other table?

Thanks for your help
Re: Counting in a subquery [message #210423 is a reply to message #210420] Wed, 20 December 2006 15:17 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
No table descriptions, so here is my answer.
select a.account_number, count(*) cts
from accounts a, charges c
where a.account_number=c.account_number
/
Re: Counting in a subquery [message #210424 is a reply to message #210423] Wed, 20 December 2006 15:21 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
Sorry,

The Charges and Statements table has an AccountID.

So i want to pull up information from from the account table based on a set of criteria, and want to display the number of Charges as a column and the number of Statements in another column.

Hope that make a little more sense.

Each account could have any amount of either.

I dont really care about displaying any information from either of the Charges/Statements tables either, just the counts.

Account Charges Statements
1 2 3
2 5 1
3 6 1
4 1 2

[Updated on: Wed, 20 December 2006 15:28]

Report message to a moderator

Re: Counting in a subquery [message #210428 is a reply to message #210420] Wed, 20 December 2006 15:49 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
SELECT AccountID, SUM(CHARGE_CNT) CHARGE_CNT, SUM(STATEMENTS_CNT) STATEMENTS_CNT
FROM
(select a.AccountID, count(*) CHARGE_CNT,0 STATEMENTS_CNT
from accounts a, charges c
where a.AccountID=c.AccountID
GROUP BY AccountID
UNION ALL
select a.AccountID, 0,count(*)
from accounts a, STATEMENTS c
where a.AccountID=c.AccountID
GROUP BY AccountID)
Previous Topic: insert into nested table from subquery
Next Topic: GPM
Goto Forum:
  


Current Time: Fri Dec 09 19:44:45 CST 2016

Total time taken to generate the page: 0.09360 seconds