CODEWITHSUNDEEP
sql-serversql-server-2008
insistent
insistent

Reputation: 3

left join to get sum of column in second table

How to get sum of Collection.Amount from table Collection provided that there aren't entries for all JId but only for those JId which have some payment against them

ALTER PROCEDURE dbo.test
AS
select JobCard.JId, sum(cast(isnull(Collection.Amount, 0) as bigint)) as [Amount] 
from JobCard left join Collection on JobCard.JId=Collection.JId 
group by JobCard.JId, Collection.Amount 
order by JobCard.JId

Upvotes: 0

Views: 133

Answers (2)

Pedram
Pedram

Reputation: 6508

I think you should write your query like this, This will give you perfect result.

    ALTER PROCEDURE dbo.test
AS
    SELECT J.JId,
           SUM(CAST(ISNULL(C.Amount, 0) AS BIGINT)) AS [Amount]
    FROM   JobCard J
           LEFT JOIN COLLECTION C
                ON  J.JId = C.JId
    GROUP BY
           J.JId
    ORDER BY
           J.JId

Need to remove Collection.Amount from group by clause.

Upvotes: 0

laylarenee
laylarenee

Reputation: 3284

Change your GROUP BY clause to:

group by JobCard.JId

Upvotes: 1

Related Questions