Need to combine 2 records into one

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need to combine 2 records into one

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-06-20 : 14:03:03

I have one table that has types of fines in it.
It has lastName, FirstName, TypeID, courtCost, Fine, Totalcost, DocketNum, CaseID

There will be 2 records that has the same DocketNum, CaseID. The one type ID, I need to get lastName, FirstName, TypeID, courtCost, Fine. The second I need to get Totalcost

So here are the 2 records
lastName, FirstName, TypeID, courtCost, Fine, Totalcost, DocketNum, CaseID

Smith, Bob, 1, 34.00, 400.00, 0 , 345678, 8787
Smith, Bob, 2, 0, 0, 60.00 , 345678, 8787

So I need to only bring back one record like so.
Smith, Bob, 34.00, 400.00, 60.00 , 345678, 8787

Notice I added the 60.00 from TypeID 2
What would the SQL look like?

Dave
Helixpoint Web Development
http://www.helixpoint.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 14:12:06

SELECT lastName,firstName,sum(courtCost) as courtCost,sum(fine) as fine,sum(TotalCost) as TotalCost
,DocketNum,CaseID
FROM youtTable
GROUP BY lastName,firstName,DocketNum,CaseID

Everyday I learn something that somebody else already knew

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2012-06-20 : 14:22:26

But there are other TypeID with the same DocketNum and CaseID. I need just trye 1 and 2

Dave
Helixpoint Web Development
http://www.helixpoint.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-20 : 14:35:43

SELECT lastName,firstName,sum(courtCost) as courtCost,sum(fine) as fine,sum(TotalCost) as TotalCost
,DocketNum,CaseID
FROM youtTable
WHERE typeID in (1,2)
GROUP BY lastName,firstName,DocketNum,CaseID

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources