Home » SQL & PL/SQL » SQL & PL/SQL » Summing Results of Other Columns
Summing Results of Other Columns [message #328965] Mon, 23 June 2008 10:16 Go to next message
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
Junior Member
Hello!

I'm still kind of new to SQL and working on finding all of the intricacies, so please bear with me!

I'm writing a query that returns one person per row. On that row, it has relevant personal information. But the last three columns are as follows:

1: Sum of specific types of charges
2: Sum of specific types of payments
3: The difference between the two prior columns

I have the first and second of these columns running correctly, but my google-fu has not turned up a simple way to calculate the third column. At this point, the only way I can think to make this work is to take the SELECT statements that track down the charges and payments and subtract them as the arguments to a SELECT statement for the third column. But that seems to clunky.

Is there an easier way?

Select 
 distinct(spriden_id) "ID", 
 rpratrm_term_code "TERM", 
 spriden_last_name "L_NAME", 
 spriden_first_name "F_NAME",
 rcrapp2_pell_pgi "EFC", 
  (Select 
     sum(dd.tbraccd_amount)
   from 
     tbraccd bb, 
     rfrbase, 
     robinst
   where 
         bb.tbraccd_pidm=aa.rpratrm_pidm
     and bb.tbraccd_term_code=robinst_current_term_code
     and (rfrbase_ftyp_code like 'SCHL' 
     or  rfrbase_ftyp_code like 'GRNT')
     and bb.tbraccd_detail_code=rfrbase_detail_code) "AWRD",
   (Select 
     sum(ee.tbraccd_amount)
    from 
     tbraccd ee, 
     tbbdetc, 
     robinst
    where 
         ee.tbraccd_pidm=aa.rpratrm_pidm
     and ee.tbraccd_term_code=robinst_current_term_code
     and tbbdetc_dcat_code=***THIS WILL BE FILLED IN LATER***
     and ee.tbraccd_detail_code=tbbdetc_detail_code) "TU_FEE",
***THIRD COLUMN GOES HERE***
from 
 rpratrm aa, 
 spriden, 
 rcrapp1, 
 rcrapp2, 
 rorstat, 
 robinst
Where 
     aa.rpratrm_pidm=spriden_pidm
 and rcrapp1_pidm=aa.rpratrm_pidm
 and rcrapp2_pidm=aa.rpratrm_pidm
 and rorstat_pidm=aa.rpratrm_pidm
 and aa.rpratrm_aidy_code='0809'
 and rcrapp1_aidy_code=aa.rpratrm_aidy_code
 and rcrapp2_aidy_code=aa.rpratrm_aidy_code
 and rorstat_aidy_code=aa.rpratrm_aidy_code
 and robinst_aidy_code=aa.rpratrm_aidy_code
 and aa.rpratrm_term_code='200920'
 and aa.rpratrm_fund_code='CLGPRM'
 and rcrapp1_curr_rec_ind='Y'
 and rcrapp2_seq_no=rcrapp1_seq_no
Re: Summing Results of Other Columns [message #328967 is a reply to message #328965] Mon, 23 June 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select ..., sum1, sum2, sum1-sum2
from (<your query>)
/

Regards
Michel
Re: Summing Results of Other Columns [message #328969 is a reply to message #328965] Mon, 23 June 2008 10:29 Go to previous messageGo to next message
doicomehereoften1
Messages: 13
Registered: June 2008
Location: CO
Junior Member
It's really that easy?
Shocked
That actually works?

Do I just use the column name aliases?

Thanks!

[Updated on: Mon, 23 June 2008 10:31]

Report message to a moderator

Re: Summing Results of Other Columns [message #328971 is a reply to message #328969] Mon, 23 June 2008 10:33 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes for all.

Regards
Michel
Previous Topic: Executing procedure from SQL server via linked server
Next Topic: Encountered : Ora 29259 while capturing emails from a Google POP server
Goto Forum:
  


Current Time: Sun Dec 04 10:59:04 CST 2016

Total time taken to generate the page: 0.14487 seconds