Home » SQL & PL/SQL » SQL & PL/SQL » Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb)
Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361303] Tue, 25 November 2008 18:13 Go to next message
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 #361312 is a reply to message #361303] Tue, 25 November 2008 21:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Select the distinct account_number and balance in a sub-query and use that sub-query as an inline view in the from clause, then select your sum from that in an outer select.
Re: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361313 is a reply to message #361303] Tue, 25 November 2008 21:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Why Not ?

select SUM(balance) from (
select distinct account_number,Balance    from test)



oops.. didn't notice barbara's reply ... My Appologies.

Smile
Rajuvan.

[Updated on: Tue, 25 November 2008 21:26]

Report message to a moderator

Re: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361320 is a reply to message #361313] Tue, 25 November 2008 23:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My Appologies.

And also for providing a full answer to a newbie question.

Regards
Michel
Re: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361368 is a reply to message #361303] Wed, 26 November 2008 01:47 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

NO .. No Apologies for that ./fa/1985/0/

Rajuvan
Re: Sum Field Based Off Distinct Other Field (2 cross-posts merged by bb) [message #361384 is a reply to message #361368] Wed, 26 November 2008 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not just
SELECT SUM(DISTINCT balance) FROM TEST;


SQL> with src as (select 100 bal from dual union all
  2               select 100 from dual union all
  3               select 200 from dual)
  4  select sum(distinct bal) from src;

SUM(DISTINCTBAL)
----------------
             300
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 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
JRowbottom wrote on Wed, 26 November 2008 01:07
Why not just
SELECT 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>

Previous Topic: group by with tricks
Next Topic: ORCL 28009
Goto Forum:
  


Current Time: Wed Dec 07 04:55:15 CST 2016

Total time taken to generate the page: 0.10737 seconds