| Counting in a subquery [message #210420] |
Wed, 20 December 2006 14:26  |
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  |
joy_division
Messages: 4963
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 |
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 |
Bill B
Messages: 1971
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
