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

Answer 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