Help me in AVG - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help me in AVG

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:02:40

Hello,
I am having a doubt in AVG.
1. I have table like this, and time is in seconds
-----------------------------------------------
MatchID | PlayerID | SegmentID | Time | Value
-----------------------------------------------
8 | 1 | 0 | 1.70 | 2.35
8 | 1 | 0 | 3.80 | 3.33
8 | 1 | 0 | 7.90 | 6.95
8 | 1 | 1 | 10.70| 9.85
8 | 1 | 1 | 17.70| 0.55
8 | 1 | 1 | 31.70| 1.45
----------------------------------------------
From the above table i have to convert the data to 10 secs or one minute data.
i.e, For ex: I done the summing here for 10 secs.
-----------------------------------------------
MatchID | PlayerID | SegmentID | Time | Value
-----------------------------------------------
8 | 1 | 0 | 10 | 12.35
--------------------------------------------------
in the same way i need to do average.
ie, i have to do average for every one minute. This i can do easily.
But if i want to show the average for overall match how can i show.
Before converting data to 10 sec or one min we can show data like avg(value). but after converting how can i show the average value.
While doing sum there is no problem.

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 07:05:43

can you explain how do you get this
"8 | 1 | 0 | 10 | 12.35"

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:17:38

Based on the matchid,playerid and segmentid i did the sum
2.35 + 3.80 + 7.90

Solutions are easy. Understanding the problem, now, that's the hard part

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 07:21:40

quote:
Originally posted by ganeshkumar08

Based on the matchid,playerid and segmentid i did the sum
2.35 + 3.80 + 7.90

Solutions are easy. Understanding the problem, now, that's the hard part

2.35 is Value
3.80 is Time
7.90 is Time
you are adding Value + Time + Time ? ? ?

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:26:42

sorry i need to add only time, i copied and pasted, form that i did mistake....

Solutions are easy. Understanding the problem, now, that's the hard part

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 07:29:20

quote:
Originally posted by ganeshkumar08

sorry i need to add only time, i copied and pasted, form that i did mistake....

Solutions are easy. Understanding the problem, now, that's the hard part

quote:

MatchID | PlayerID | SegmentID | Time | Value
-----------------------------------------------
8 | 1 | 0 | 10 | 12.35

OK. assuming you are adding time . .
1.7 + 3.8 + 7.9 = 13.4

Where is the 10 comes from ?
And how about the 12.35 ?

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:34:21

from 0-10 secs sum is 12.35, here we can display as 1 for first 10 sec and 2 for second 10 sec etc..

Solutions are easy. Understanding the problem, now, that's the hard part

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 07:43:45

quote:
Originally posted by ganeshkumar08

from 0-10 secs sum is 12.35, here we can display as 1 for first 10 sec and 2 for second 10 sec etc..

Solutions are easy. Understanding the problem, now, that's the hard part

how are you getting this 12.35? can you explain that?

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:45:26

sum of value for first 10 sec..

Solutions are easy. Understanding the problem, now, that's the hard part

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 07:46:37

quote:
Originally posted by ganeshkumar08

from 0-10 secs sum is 12.35, here we can display as 1 for first 10 sec and 2 for second 10 sec etc..

Sorry . . i am totally lost

KH
[spoiler]Time is always against us[/spoiler]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 07:46:46

sorry the total value is 12.63

Solutions are easy. Understanding the problem, now, that's the hard part

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-07-08 : 07:53:32

quote:
Originally posted by ganeshkumar08

sorry the total value is 12.63

Solutions are easy. Understanding the problem, now, that's the hard part

your explanations not that clear to understand.,

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-08 : 08:04:16

ganeshkumar is working hard to make his favourite Quote a self-fulfilling prophecy ;)

(54224 row(s) affected)

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 09:15:54

Is this what you want ?

If not, show us what is your expected result and please include the explanation and formula on how you obtain the result

DECLARE @TABLE TABLE
(
	MatchID		int,
	PlayerID	int,
	SegmentID	int,
	[Time]		decimal(10,2),
	Value		decimal(10,2)
)
INSERT INTO @TABLE
SELECT	8 , 1 , 0 , 1.70 , 2.35 UNION ALL
SELECT	8 , 1 , 0 , 3.80 , 3.33 UNION ALL
SELECT	8 , 1 , 0 , 7.90 , 6.95 UNION ALL
SELECT	8 , 1 , 1 , 10.70, 9.85 UNION ALL
SELECT	8 , 1 , 1 , 17.70, 0.55 UNION ALL
SELECT	8 , 1 , 1 , 31.70, 1.45

SELECT	s.MatchID, s.PlayerID, s.SegmentID, 
	s.SumTime, s.SumValue, 
	a.AvgTime, a.AvgValue
FROM
(
	SELECT	MatchID, PlayerID, SegmentID, SumTime = SUM(Time), SumValue = SUM(Value)
	FROM	@TABLE
	GROUP BY MatchID, PlayerID, SegmentID
) s
INNER JOIN
(
	SELECT	MatchID, PlayerID, SegmentID, AvgTime = avg(Time), AvgValue = avg(Value)
	FROM	@TABLE
	GROUP BY MatchID, PlayerID, SegmentID
) a	ON	s.MatchID	= a.MatchID
	AND	s.PlayerID	= a.PlayerID
	AND	s.SegmentID	= a.SegmentID

/*
MatchID     PlayerID    SegmentID      SumTime     SumValue     AvgTime     AvgValue                                 
----------- ----------- ----------- ---------- ------------ ------------ ----------- 
          8           1           0      13.40        12.63     4.466666    4.210000 
          8           1           1      60.10        11.85    20.033333    3.950000 

(2 row(s) affected)
*/