how to use sum function in sql joins

Question

How to use sum function in joins in sql server 2008?

SELECT SUM(d.TranTypeAmt),
       h.LnNo,
       h.LoanRcptAmt,
       d.Trantype,
       d.TranTypeAmt
FROM   LGen_LnInstClln_h h
       RIGHT OUTER JOIN LGen_LnInstClln_d d
         ON h.PK_Id = d.InstCllnHdr_FK
WHERE  h.LnNo = '40009'

Your WHERE clause turns the right outer join into an inner join. — Martin Smith
– Martin Smith, Commented Jul 18, 2012 at 11:10

Curtis · Accepted Answer · 2012-07-18 11:09:58Z

2

When using an aggregate function, such as SUM() you need to either use aggregate functions on the rest of the data items, or group by them.

select  SUM(d.TranTypeAmt), h.LnNo,h.LoanRcptAmt,d.Trantype,d.TranTypeAmt
from LGen_LnInstClln_h h 
right outer join LGen_LnInstClln_d d on h.PK_Id=d.InstCllnHdr_FK  
where h.LnNo='40009'
GROUP BY h.LnNo,h.LoanRcptAmt,d.Trantype,d.TranTypeAmt

answered Jul 18, 2012 at 11:09

Curtis

104k69 gold badges280 silver badges359 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Black Over a year ago

The question says nothing about the cardinality of the relation between the tables. If one PK_Id matches multiple d.InstCllnHdr_FK, then the SUM function will sum up those multiple matches. Is this what kalaivanan wants?

kalaivanan Over a year ago

by using the above query following error is displaying olumn 'LGen_LnInstClln_h.LnRcptNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Josien Over a year ago

@kalaivanan: h.LnRcptNo is not part of the SELECT list in this query. I think you accidentally changed it. What happens when you execute the query as it is, without changing anything?

Joe G Joseph · Accepted Answer · 2012-07-18 11:12:15Z

0

You should have a group by clause when using aggregate function, with other columns in the select list.

so here you should add

group by 
h.LnNo,
       h.LoanRcptAmt,
       d.Trantype,
       d.TranTypeAmt

at the end of the query

answered Jul 18, 2012 at 11:12

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

Collectives™ on Stack Overflow

how to use sum function in sql joins

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related