Average Speed based on condition

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

Average Speed based on condition

Author

Topic

KunalSinghTomar
Starting Member

2 Posts

Posted - 2011-11-15 : 01:53:08

Hello Everyone,

I need help to calulate average speed using distance/No of Rows.

I have table which store every second speed and odometer value in table.I want to calulate Average speed based on condition.Example
Average speed when speed>50 or average speed when speed<60 . problem is that when i write query with speed>70 it bring those rows which in which speed was less then 70 between two speed which are greater then 70. In this case no of rows are correct but odometer value is not correct as we want exclude odometer value when speed is less then 70 between two greater then 70 speed rows. Please chekc table

Odometer Speed
100 40
120 40
130 40
140 60
150 60
160 60
190 70
200 70
210 40
220 40
250 70
280 70
300 30
340 60
350 30
360 30

What is Average Speed when Speed >50
What is Average Speed when Speed <50
What is Average Speed when Speed =30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-15 : 01:58:34

quote:
What is Average Speed when Speed =30

This does not make sense. Why do you need to find the average speed and the speed is constant at 30 ?

Maybe this is a trick question in your assignment ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 03:11:53

do you mean this?


SELECT
AVG(CASE WHEN Speed>50 then Speed ELSE NULL END) AS [Avg>50],
AVG(CASE WHEN Speed<50 then Speed ELSE NULL END) AS [Avg<50],
..
FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KunalSinghTomar
Starting Member

2 Posts

Posted - 2011-11-15 : 06:43:09

Hello All,

when i calculate Avg speed i use this calulation
max(odometer)-min(odometer) /Divided by no of rows

Ex:-

Speed Odometer
60 100
60 120
40 125
60 135

in this case if i use Speed>50 then i will get 3 rows but
Max(Odometer)-Min(Odometer) will be 135-100 which is wrong as this consider odomter value which was generated when speed was 40.
Note:- when i calculate distance it should not consider distance when speed reached 60 to 40 then from 40 to 60.

Hope now it is clear .
Thanks to you all for spending time on my problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 06:53:00

quote:
Originally posted by KunalSinghTomar

Hello All,

when i calculate Avg speed i use this calulation
max(odometer)-min(odometer) /Divided by no of rows

Ex:-

Speed Odometer
60 100
60 120
40 125
60 135

in this case if i use Speed>50 then i will get 3 rows but
Max(Odometer)-Min(Odometer) will be 135-100 which is wrong as this consider odomter value which was generated when speed was 40.
Note:- when i calculate distance it should not consider distance when speed reached 60 to 40 then from 40 to 60.

Hope now it is clear .
Thanks to you all for spending time on my problem.

sorry i didnt understand this
how will your max value (135) and min value(100) get affected even if you include or exclude row with speed = 40?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources