Compare two rows & add results

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

General SQL Server Forums

New to SQL Server Programming

Compare two rows & add results

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2011-03-17 : 15:59:18

Hey all. I've been doing some searching and testing but have had no luck so I'm throwing in the towel and asking for help.

I have a table that tracks Participant's number of apples (trying to keep it simple).

Each row in the table tracks the row ID, participant ID, and number of apples.

I'd like to find the first record for the participant and add the number of apples to the last row for that same participant. So this participant could have 50 rows of data but I just want the first and last.

Ex.


rowID..participantID..apples
1..A..5
2..A..3
3..B..6
4..B..2
5..A..2
6..B..10

Participant A would have 7 apples (row 1 has five and row 5 has two).
Participant B would have 16 apples (row 3 has six and row 6 has ten).

Any help would be appreciated. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-17 : 16:16:59

[code];WITH Rows(Participant, MinID, MaxID) AS (
SELECT ParticipantID, Min(RowID), Max(RowID)
FROM myTable
GROUP BY ParticipantID)
SELECT R.Participant, Sum(A.Apples)
FROM Rows R
INNER JOIN myTable A ON R.RowID=A.RowID
GROUP BY R.Participant[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources