Summing Results of Other Columns [message #328965] |
Mon, 23 June 2008 10:16  |
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
|
|
|
|
|
|