Huge aggregate function help - SQL Server Forums

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

Huge aggregate function help

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-28 : 16:33:27

Let's say I have table that tracks each person's weight over time.
tblAssessment
recordID, PersonID, Weight

ex data.
1, 001, 150
2, 002, 200
3, 001, 145
4, 002, 190
5, 003, 110
6, 003, 115

How can I get the average weight change amongst all people based on their first and last assessment?
001 = 150 - 145 = 5
002 = 200 - 190 = 10
003 = 110 - 115 = -5 (gained 5lbs)

5 + 10 - 5 = 10
10 / 3 = 3.33

Meaning, the average person loses 3.33 lbs.

select avg((weight from min record) - (weight from max record))
from tblassessment

...ugh

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-28 : 16:45:48

Try this:

DECLARE @tblAssessment TABLE (RecordID INT, PersonID INT, [Weight] INT)

INSERT @tblAssessment
SELECT 1, 001, 150
UNION ALL SELECT 2, 002, 200
UNION ALL SELECT 3, 001, 145
UNION ALL SELECT 4, 002, 190
UNION ALL SELECT 5, 003, 110
UNION ALL SELECT 6, 003, 115


SELECT
	AVG(A.[Weight] * 1.00 - B.[Weight])
FROM
	(
		SELECT 
			PersonID,
			[Weight],
			ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RecordID) AS RowStart
		FROM
			@tblAssessment
	) AS A
INNER JOIN
	(
		SELECT 
			PersonID,
			[Weight],
			ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RecordID DESC) AS RowEnd
		FROM
			@tblAssessment
	) AS B
	ON A.PersonID = B.PersonID
	AND A.RowStart = 1
	AND B.RowEnd = 1

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-28 : 17:02:04

Lamprey -
I got a result from that with no errors. Looks good so far. Now I have to do some manual testing.

What will happen if the Person only has one record? Will that query take the same record, subtract them from eachother, and then try to add that person to the overall average?

I think that might lower the total average.

edit* I assume i would need something like

when a.row_number() <> b.row_number()

but with all of my iterations of testing with that type of statement it breaks =)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-28 : 17:35:49

Yeah, that's all you need to do is add in the RecordID:

DECLARE @tblAssessment TABLE (RecordID INT, PersonID INT, [Weight] INT)

INSERT @tblAssessment
SELECT 1, 001, 150
UNION ALL SELECT 2, 002, 200
UNION ALL SELECT 3, 001, 145
UNION ALL SELECT 4, 002, 190
UNION ALL SELECT 5, 003, 110
UNION ALL SELECT 6, 003, 115
UNION ALL SELECT 7, 006, 115


SELECT
	AVG(A.[Weight] * 1.00 - B.[Weight])
FROM
	(
		SELECT 
			RecordID,
			PersonID,
			[Weight],
			ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RecordID) AS RowStart
		FROM
			@tblAssessment
	) AS A
INNER JOIN
	(
		SELECT 
			RecordID,
			PersonID,
			[Weight],
			ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RecordID DESC) AS RowEnd
		FROM
			@tblAssessment
	) AS B
	ON A.PersonID = B.PersonID
	AND A.RecordID <> B.RecordID
	AND A.RowStart = 1
	AND B.RowEnd = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 17:44:43

Something similar to this?

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		rowID INT,
		PersonID INT,
		Weight INT
	)

INSERT	@Sample
SELECT	1, 1, 150 UNION ALL
SELECT	2, 2, 200 UNION ALL
SELECT	3, 1, 145 UNION ALL
SELECT	4, 2, 190 UNION ALL
SELECT	5, 3, 110 UNION ALL
SELECT	6, 3, 115

-- Prepare staging
DECLARE	@Stage TABLE
	(
		PersonID INT,
		firstWeight INT,
		lastWeight INT,
		Loss AS (lastWeight - firstWeight)
	)

INSERT		@Stage
		(
			PersonID,
			firstWeight,
			lastWeight
		)
SELECT		PersonID,
		MAX(CASE WHEN first = 1 THEN Weight ELSE NULL END) AS first,
		MAX(CASE WHEN last = 1 THEN Weight ELSE NULL END) AS last
FROM		(
			SELECT	PersonID,
				Weight,
				ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RowID) AS first,
				ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY RowID DESC) AS last
			FROM	@Sample
		) AS d
WHERE		1 IN (first, last)
GROUP BY	PersonID

SELECT	*
FROM	@Stage

SELECT	MIN(Loss),
	MAX(Loss),
	AVG(1.0E * Loss)
FROM	@Stage

E 12°55'05.63"
N 56°04'39.26"

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-28 : 17:58:02

Lamprey -
That did it! I'll be able to use this for a ton of metrics. Huge thanks.

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-29 : 13:45:26

I've started a new topic on how to insert the results of this query into a temporary table at which point I would run another query just like this and insert the updated data into the same temporary table.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=126725

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources