CODEWITHSUNDEEP
sqlsql-server-2012
Crezzer7
Crezzer7

Reputation: 2335

SQL Server -Adding values between tables

Right, my scenario is this: I have 3 tables (Finances, CommonCosts, OtherCosts)

The Total will calculate the fields based upon the FinanceID

my problem is this:

using the below SQL Query, if I dont have a field in OtherApplications then the OverallTotal will not display in the view I am using... 

SELECT 
sum(ProjectFinances.PFTotal+ OtherApplications.OTotal)AppTotal
from ProjectFinances
inner join OtherApplications on ProjectFinances.FinanceID = OtherApplications.FinanceID
group by ProjectFinances.FinanceID

the below example shows the issue, is there a way to solve this easily? its basically the fact that there is no value within the OtherApplications table to add so it doesnt bother displaying it, I want it to add 0 if it doesnt exist... thanks guys

enter image description here

Upvotes: 0

Views: 33

Answers (2)

Brian Driscoll
Brian Driscoll

Reputation: 19635

The SUM function is used to add values within the same column. If you want to add two different columns, just remove the SUM() reference. Also, if it is possible there is no value in OtherApplications, you should use an outer join and replace NULL with 0. Put it all together and you have:

SELECT 
ProjectFinances.PFTotal + ISNULL(OtherApplications.OTotal, 0) as AppTotal
from ProjectFinances
left outer join OtherApplications on ProjectFinances.FinanceID = OtherApplications.FinanceID

Inner vs Outer Join

Inner join will show values only if there is a row in the right-side table matching a value in the left-side table for the join condition. In your case, that means that you will only get a result if there is a row in the OtherApplications table with the same value for FinanceID as a row in the ProjectFinances table.

An outer join will show values for either or both tables depending upon the type of outer join you use, plus matching rows for the other side of the join (otherwise NULL). For a LEFT outer join, all values from the LEFT side table will be shown, and either a matching value from the right side table if the join condition is met or NULL otherwise. A RIGHT outer join is similar - except that we'd expect to see all values from the RIGHT side table and either a matching value from the left side table if the join condition is met or NULL otherwise. In your case, the LEFT side table is ProjectFinances and the RIGHT side table is OtherApplications. The last type of outer join is called a FULL outer join, and it will basically give you the distinct union of a LEFT outer join and a RIGHT outer join. In other words, it would give you all rows from both ProjectFinances and OtherApplications, showing matching values where the join condition is met or NULL otherwise on each respective side of the join.

Upvotes: 2

Youness
Youness

Reputation: 1495

try this (UNTESTED) :

  SELECT 
    SUM(ISNULL(ProjectFinances.PFTotal,0)+ ISNULL(OtherApplications.OTotal,0))AppTotal
    from ProjectFinances
    inner join OtherApplications on ProjectFinances.FinanceID = OtherApplications.FinanceID
    group by ProjectFinances.FinanceID

PS : If there is a valueits added if else its gives 0 as you requested i dont realy know what you are trying to do here but thats what you asked for ;) hopr it helps .

Upvotes: 0

Related Questions