Home » SQL & PL/SQL » SQL & PL/SQL » How do I total 2 total columns in this SQL??
How do I total 2 total columns in this SQL?? [message #225625] Tue, 20 March 2007 10:37 Go to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
How do I add the columns in this sql that are commented out?
Thanks!


SELECT MEMB.MEMB_APPEAL_CODE, Count(MEMB_ENTITY.MEMB_NUMBER) AS AI, MEMB.MEMB_TYPE_CODE,

(SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdMTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE to_char(m.start_date,'MM') || to_char(m.start_date,'YYYY') = to_char(sysdate,'MM')
|| (to_char(sysdate,'YYYY')-1 )
and m.memb_appeal_code = 'AGS' and m.memb_type_code = 'AI') AMT_PD_MTD,

(SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdYTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'

AND to_char(m.start_date, 'YYYY') = (case when to_char(m.start_date,'MM') < 7 then
to_char(sysdate,'YYYY')-1
else to_char(sysdate,'YYYY')+1
end)) AMT_PD_FY_YTD,


--(AMT_PD_MTD) + (AMT_PD_FY_YTD) Amt_Pd_Total,


(SELECT Sum(m.MEMB_AMT_PAID) AS AmtDueYTD
FROM MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER
INNER JOIN MEMB_PAY_SCHEDULE ON m.MEMB_NUMBER = MEMB_PAY_SCHEDULE.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND MEMB_PAY_SCHEDULE.SCHED_STATUS_CODE = 'U'
AND to_char(m.start_date, 'YYYY') =
(case when to_char(m.start_date,'MM') < 7 then
to_char(sysdate,'YYYY')-1
else to_char(sysdate,'YYYY')+1
end)) AS Amt_Due_FYTD,

(SELECT Sum(m.MEMB_AMT_PAID) AS AmtPdYTD
FROM (MEMB m INNER JOIN MEMB_ENTITY me ON m.MEMB_NUMBER = me.MEMB_NUMBER)
INNER JOIN MEMB_PAY_SCHEDULE ON m.MEMB_NUMBER = MEMB_PAY_SCHEDULE.MEMB_NUMBER
WHERE m.MEMB_APPEAL_CODE = 'AGS'
AND m.MEMB_TYPE_CODE = 'AI'
AND MEMB_PAY_SCHEDULE.SCHED_STATUS_CODE = 'U') AS Amt_Due_Total--,


--[Amt_Pd_FY_YTD]+[Amt_Due_FYTD] AS Total_FY_YTD,

--[Amt_Pd_Total]+[Amt_Due_Total] AS OverAllTotal



FROM MEMB INNER JOIN MEMB_ENTITY ON MEMB.MEMB_NUMBER = MEMB_ENTITY.MEMB_NUMBER
GROUP BY MEMB.MEMB_APPEAL_CODE, MEMB.MEMB_TYPE_CODE
HAVING (((MEMB.MEMB_APPEAL_CODE)='AGS')
AND ((MEMB.MEMB_TYPE_CODE)='AI'))
Re: How do I total 2 total columns in this SQL?? [message #225651 is a reply to message #225625] Tue, 20 March 2007 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might try to "convert" your existing main query into an inline view and do something like this:
select x.memb_appeal_code, x.amt_pd_fy_ytd + x.amt_pd_mtd here_is_the_summary
from 
   (select memb_appeal_code,            -- here starts your existing query ...
          (select sum(memb_amt_paid) 
           from ...
           where ...
          ) amt_pd_fy_ytd,
          (select sum(something else)
           from ...
           where
          ) amt_pd_mtd
   from ...
   where ...                            -- ... and here it ends
   ) x;      
icon7.gif  Re: How do I total 2 total columns in this SQL?? [message #225652 is a reply to message #225651] Tue, 20 March 2007 13:50 Go to previous messageGo to next message
mxd198
Messages: 28
Registered: December 2005
Junior Member
ok. I will try that. Thanks!
Re: How do I total 2 total columns in this SQL?? [message #225782 is a reply to message #225625] Wed, 21 March 2007 08:21 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Dude, you are STILL comparing character strings to numbers and performing math functions on character strings. Would you stop doing that? You are going to be sorry when you get incorrect results. This should not be posted in the expert forum if you are going to keep making newbie mistakes like that.

[Updated on: Wed, 21 March 2007 08:23]

Report message to a moderator

Previous Topic: fine tuning SQL
Next Topic: DBTIMEZONE questions
Goto Forum:
  


Current Time: Sat Dec 10 22:17:32 CST 2016

Total time taken to generate the page: 0.26738 seconds