Help with total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with total

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-10-29 : 11:23:05

HI everyone, I been looking for the answer for sometime now, thought i would post and see if anyone could help me it. This is the output of my query.

F_Div Div_4 Div_5 Div_35
4 246 0 0
5 0 400 0
35 1 0 114

After the row 35 i want to add a row for total. I want to add the values of column Div_4 so it would be 246+0+1. The problem i'm having is that the columns are populated from using this logic.

Select 
rr.F_BVT_DIV_NO,
(Case When rr3.F_DIVISION_NO = 4  then Max(rr3.Total_Pol_Count) else 0 end ) + (MAX(isnull(rr2.Div4,0))) Div_4

from RNEWL_RTENTN_DETL RR
--join for division 4 transfers

left join(

select
rr2.F_BVT_DIV_NO,
count(rr2.X_POLICY_NO) As Div4
from RNEWL_RTENTN_DETL RR2 
where
RR2.F_BVT_DIV_NO<>RR2.F_DIVISION_NO
 and month(RR2.X_POLICY_XPRTN_DT)=2 and
 year(RR2.X_POLICY_XPRTN_DT)=2007 and
 rr2.F_DIVISION_NO = 4
 Group By
 rr2.F_BVT_DIV_NO ) rr2 on
 rr2.F_BVT_DIV_NO = rr.F_BVT_DIV_NO

from TRNEWL_RTENTN_DETL rr3
where 
month(rr3.X_POLICY_XPRTN_DT)=2 and
year(rr3.X_POLICY_XPRTN_DT)=2007
Group by 
rr3.F_DIVISION_NO) rr3 on
rr3.F_DIVISION_NO = rr.F_BVT_DIV_NO
group by 
rr.F_BVT_DIV_NO,
rr3.F_DIVISION_NO
order by
rr.F_BVT_DIV_NO

That code is just a little piece of it, but the logic is the same throughout. Thanks for the input!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-10-29 : 21:56:31

You can use your query as a derived table subquery, so essentially do this

SELECT sum(Div_4)
FROM (your query here) AS MyQuery

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources