Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361303] |
Tue, 25 November 2008 18:13  |
tloftus
Messages: 1 Registered: November 2008
|
Junior Member |
|
|
I'm wondering if there's a way to sum a field based off another field being distinct (one you are not summing).
Example (in every case the account number and balance will be the same):
Account_Number Balance Attempt_Number
11111 100 1
11111 100 2
22222 500 1
22222 500 2
22222 500 3
33333 100 1
Attempt Balances:
SELECT SUM(BALANCE)
=> 1800
Distinct Accounts:
SELECT COUNT(DISTINCT ACCOUNT_NUMBER)
=> 3
Sum of Distinct Account Balances:
????????????
=> 700
From what I know I will probably have to use some sort of Sub-Query but wondering if there's some function or combination of functions I am not thinking of.
Thanks,
Tom
[Updated on: Tue, 25 November 2008 18:14] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361502 is a reply to message #361384] |
Wed, 26 November 2008 12:18  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
JRowbottom wrote on Wed, 26 November 2008 01:07 | Why not justSELECT SUM(DISTINCT balance) FROM TEST;
|
Because it produces the wrong answer. The original poster specified that it should be the distinct balance for each account_number, not just the distinct balance. Notice the differences below.
-- test data:
SCOTT@orcl_11g> SELECT *
2 FROM test
3 ORDER BY account_number, attempt_number
4 /
ACCOUNT_NUMBER BALANCE ATTEMPT_NUMBER
-------------- ---------- --------------
11111 100 1
11111 100 2
22222 500 1
22222 500 2
22222 500 3
33333 100 1
6 rows selected.
-- correct:
SCOTT@orcl_11g> SELECT SUM (balance)
2 FROM (SELECT DISTINCT account_number, balance
3 FROM test)
4 /
SUM(BALANCE)
------------
700
-- wrong:
SCOTT@orcl_11g> SELECT SUM (DISTINCT balance)
2 FROM test
3 /
SUM(DISTINCTBALANCE)
--------------------
600
SCOTT@orcl_11g>
|
|
|