sum without dates in query

hi

this is my code :

select C.FullName,L.LoanKey,L.OpenDate,L.LoanSum,
SUM(P.PayOnFund) as OnKerenPay,
isnull(SUM(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as OnInterst,
isnull(Sum(P.PayOnFund),0)+ISNULL(sum(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as TotalPay
from  dbo.Loans L
left join
dbo.LoanMoves M
on L.LoanKey=M.MainLoanKey
inner join dbo.Customers C on L.CustNum=C.CustomerKey
left join dbo.Pays P on MoveKey=LoanMoveRunId
where P.PayDate between '1/1/2010'+' 00:00:00' And '1/12/2015'+' 23:59:59' AND LoanCancelled <> 1
group by L.LoanSum,C.FullName,L.LoanKey,L.OpenDate,M.MainLoanKey


i need also the get the total sum from the query
i need to combine this code :

select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total
from  dbo.Loans
left join dbo.Pays
on LoanKey=LoanMainKey
group by LoanKey

to get the total pay for each loan row
without consider the date range in the main query
how can i do it ?
thanks ....
Tech_MenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gpizzutoCommented:
You can use "WITH CUBE" or "WITH ROLLUP" in the group by clause. They add null value to sum over the grouping fields.
ROLLUP will eliminate rows with NULL in the first grouped column.
0
Scott PletcherSenior DBACommented:
left outer join (
    select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total
    from  dbo.Loans
    left join dbo.Pays
        on LoanKey=LoanMainKey
    where LoanCancelled <> 1
    group by LoanKey
) AS totalPays ON
    totalPays.LoanKey = L.LoanKey
0
Scott PletcherSenior DBACommented:
Add the subquery above to the main query, something like this:

SELECT
    ...,
    totalPays.Total
from  dbo.Loans L
left outer join (
select LoanKey ,isnull(Sum(LoanSum),0) - ISNULL(sum(PayOnFund),0) as Total
    from  dbo.Loans
    left join dbo.Pays
        on LoanKey=LoanMainKey
    where LoanCancelled <> 1
    group by LoanKey
) AS totalPays ON
    totalPays.LoanKey = L.LoanKey
...rest of query same as before...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Tech_MenAuthor Commented:
hi thanks for your answer
i have try your code but its dosent compute and i get a wrong res
this is the code after i change it :

select C.FullName,L.LoanKey,L.OpenDate,L.LoanSum,
SUM(P.PayOnFund) as OnKerenPay,
isnull(SUM(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as OnInterst,
isnull(Sum(P.PayOnFund),0)+ISNULL(sum(P.PayOnInterest),0) + ISNULL(sum(PayFines),0) as TotalPay
from  dbo.Loans L

left outer join (

select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

) AS totalPays ON
    totalPays.LoanKey = L.LoanKey

left join
dbo.LoanMoves M
on L.LoanKey=M.MainLoanKey
inner join dbo.Customers C on L.CustNum=C.CustomerKey
left join dbo.Pays P on MoveKey=LoanMoveRunId
where P.PayDate between '1/1/2010'+' 00:00:00' And '12/01/2012'+' 23:59:59' AND LoanCancelled <> 1
group by L.LoanSum,C.FullName,L.LoanKey,L.OpenDate,M.MainLoanKey

this is the res :

dana      93             2011-11-27 00:00:00.000      10000.00      2012.66      1097.33      3110.00
simon      91      2011-11-27 00:00:00.000      10000.00      758.70      600.00      1358.70
simon      94      2011-11-27 00:00:00.000      10000.00      558.40      441.598      1000.00

the total for dana is : 3110 its wrong its need to be : 7987.33  because  the sum is 10.000 and payd 2012.66 so 10.000-2012.66 = 7987.34 i am geting 3110.00

if i do this code i get the right res  (look id 93):


select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

res :
91      9241.30
93      7987.33
94      9441.59


when i combine this whit the first query its give a wrong calculation
0
Scott PletcherSenior DBACommented:
Hmm, I don't see where you used the value in the subquery calculation in your main query at all.  

The calculated column from the inner query is named "totalPays" -- I don't see a reference to that value anywhere in the outer query (the SELECT above it).
0
Tech_MenAuthor Commented:
in this :
from  dbo.Loans L

left outer join (

select LoanKey,LoanSum - ISNULL(sum(p.PayOnFund),0) as totalPays
from dbo.Pays p left join dbo.Loans l
on LoanMainKey=LoanKey
group by LoanKey,LoanSum

) AS totalPays ON
    totalPays.LoanKey = L.LoanKey
0
Tech_MenAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.