Tutorial :SQL Average Data Based on Distance



Question:

I'm pretty new to SQL. I have a database with records based on road/milepoints. My goal is to get an average value every 52.8 ft along the road. My related table has data every 15 ft, this table of course has a foreign key relating it to the primary table.

If I wanted to pull out the average value every 52.8 ft, along a given milepost, how would I go about this?

Example Data:

      RecID   Begin_MP    End_MP        100  0  0.56        RecID    MP Value1  Value2      100      0  159      127.7      100  0.003  95.3     115.3      100  0.006  82.3       107      100  0.009  56.5      74.5      100  0.011  58.1      89.1      100  0.014  95.2      78.8      100  0.017  108.9    242.5      100   0.02  71.8      73.3      100  0.023  84.1      80.2      100  0.026  65.5      66.1      100  0.028  122      135.8      100  0.031  99.9     230.7      100  0.034  95.7     111.5      100  0.037  127.3     74.3      100   0.04  140.7    543.1  

The first Data is an example of a Road. The second subset of data are the values I need to query out every 52.8 ft.

Thank you


Solution:1

You could group the data in 52.8 feet blocks. One way to do that is to divide the distance by 52.8, and round that to a whole number. That way, 25 belongs to group 1, 100 belongs to group 2, 110 belongs to group 3, and so on.

In SQL Server, you'd write this like:

select       52.8 * cast(dist/52.8 as int) as Distance  ,   avg(value1)  ,   avg(value2)  from YourTable  group by cast(dist/52.8 as int)  

Below is an example with your data. Because the data runs from 0 to 0.04, I've made it calculate averages per 0.01 feet block:

declare @Road table (RecID int, Begin_MP float, End_MP float)  insert into @Road select 100, 0, 0.56    declare @Values table (RecID int, MP float, Value1 float, Value2 float)  insert into @Values values  (100, 0    ,   159  ,   127.7),  (100, 0.003,   95.3 ,   115.3),  (100, 0.006,   82.3 ,   107),  (100, 0.009,   56.5 ,   74.5),  (100, 0.011,   58.1 ,   89.1),  (100, 0.014,   95.2 ,   78.8),  (100, 0.017,   108.9,   242.5),  (100, 0.02 ,   71.8 ,   73.3),  (100, 0.023,   84.1 ,   80.2),  (100, 0.026,   65.5 ,   66.1),  (100, 0.028,   122  ,   135.8),  (100, 0.031,   99.9 ,   230.7),  (100, 0.034,   95.7 ,   111.5),  (100, 0.037,   127.3,   74.3),  (100, 0.04 ,   140.7,   543.1);    select          r.RecID  ,   cast(v.MP/0.01 as int)*0.01 as StartMP  ,   AVG(v.Value1) as AvgVal1  ,   AVG(v.Value2) as AvgVal2  from      @Road as r  left join @Values as v  on        r.RecID = v.RecID  group by  r.RecID, cast(v.MP/0.01 as int)  

This prints:

RecID  StartMP AvgVal1  AvgVal2  100    0.00    98,275   106,125  100    0.01    87,4     136,8  100    0.02    85,85    88,85  100    0.03    107,63   138,83  100    0.04    140,7    543,1  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »