locked
Average by GROUP BY in SQL Server RRS feed

  • Question

  • I am working of traffic data. I have following data Table. I want to get the average speed of all vehicles wih in every one seconds within position of every 10 meters. Data time interval is 0.2 seconds. Here is my data:

    Sim Time (sec) Veh No    Position (m)    Speed (km/hr)
               0.20           1             0.01          0.00
               0.40           1             2.74          34.56
               0.60           1             5.46          23.45 
               0.60           2             2.20          45.12
               0.60           3             1.30          25.12
               0.80           1             8.18          34.12
               0.80           2             5.67          20.19
               0.80           3             4.65          34.54
               1.00           1            10.91          21.15 
               1.00           2             9.14          19.28 
               1.00           3             8.00          25.12
               1.20           1            13.63          34.12
               1.20           2            12.61          28.45
               1.20           3            11.35          34.12
               1.20           4             0.87          32.11
               1.40           1            16.35          21.18 
               1.40           2            16.07          23.12
               1.40           3            14.70          24.34
               1.40           4             4.44          27.76
               1.60           1            19.08          34.12
               1.60           2            19.54          25.87 
               1.60           3            18.05          20.12
               1.60           4             8.00          34.11
               1.60           5             1.65          21.11
               1.80           1            21.80          29.34 
               1.80           2            23.01          25.98 
               1.80           3            21.40          22.31
               1.80           4            11.57          33.98          
               1.80           5             4.37          21.87
               2.00           1            24.52          29.34
               2.00           2            26.48          26.01
               2.00           3            24.75          22.98 
               2.00           4            15.14          32.91
               2.00           5             7.08          22.34
               2.20           1            27.25          28.67  
               2.20           2            29.95          25.89
               2.20           3            28.10          23.01  
               2.20           4            18.70          31.23
               2.20           5             9.79          23.03 
               2.40           1            29.97          27.67  
               2.40           2            33.42          26.56     
               2.40           3            31.44          24.12
               2.40           4            22.27          30.54
               2.40           5            12.48          24.01
               2.40           6             1.35          32.76
               2.60           1            32.69          23.34
               2.60           2            36.88          34.32
               2.60           3            34.79          32.32
               2.60           4            25.83          26.23
               2.60           5            15.16          34.21 
               2.60           6             5.20          23.21
               2.80           1            35.42          26.21
               2.80           2            40.35          31.01
               2.80           3            38.14          23.45
               2.80           4            29.40          25.74
               2.80           5            17.83          24.61
               2.80           6             9.05          28.65
               2.80           7             3.07          21.34
               2.80           8             2.10          26.34
               3.00           1            38.14          27.32

    I expect the result should be as follows:

    Sim Time(sec) Position (m)    Avg Speed (km/hr)
               1.00            10.00            29.05
               1.00            20.00            21.15 
               2.00            10.00            26.55 
               2.00            20.00            28.39
               2.00            30.00            25.99
               3.00            10.00            25.89
               3.00            20.00            28.52 
               3.00            30.00            26.82
               3.00            40.00            27.21
               3.00            50.00            31.01

    I have tried with this one also. But result ended zero records:

    WITH Temp AS 
    (   SELECT 1 as SimTime , 0 as Position
        UNION ALL
        SELECT SimTime+1, Position + 10
        FROM Temp
        WHERE Position < 10000
        )
    SELECT [Sim Time (sec)], [Position (m)], AVG([Speed (km/hr)]) as AvgSpeed from [Sim VehRecord]
    INNER JOIN Temp
    ON [Sim VehRecord].[Sim Time (sec)] = Temp.SimTime   
    WHERE [Sim Time (sec)] > Temp.SimTime AND [Sim Time (sec)] < Temp.SimTime + 1 
    AND [Position (m)] > Temp.Position AND [Position (m)] < Temp.Position+10
    GROUP BY [Sim Time (sec)], [Position (m)]



    • Edited by Tariq Hasan Thursday, April 16, 2020 4:20 PM correction
    Wednesday, April 15, 2020 2:35 PM

Answers

  • Sounds like this is what you are looking for:

    ; WITH tbl1agg AS (
       SELECT ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl1
       WHERE  Pos > 0
       GROUP  BY ceiling(Time), ceiling(Pos/10)*10
    ), tbl2agg AS (
       SELECT ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl2
       WHERE  Pos > 0
       GROUP  BY ceiling(Time), ceiling(Pos/10)*10
    )
    SELECT SUM(abs(t1.avgspeed - t2.avgspeed))
    FROM   tbl1agg t1
    JOIN   tbl2agg t2 ON t1.Time = t2.Time
                     AND t1.Pos  = t2.Pos

    I like to add that it sounds funny that you would have two tables. A better design would be to have a single table with one number column called "testrun" or whatever the two tables signify. This permits for a somewhat shorter solution:

    ; WITH aggr AS (
       SELECT testno, ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl
       WHERE  Pos > 0
       GROUP  BY testno, ceiling(Time), ceiling(Pos/10)*10
    ) SELECT SUM(abs(a1.avgspeed - a2.avgspeed))
    FROM   aggr a1
    JOIN   aggr a2 ON a1.Time = a2.Time
                  AND a1.Pos  = a2.Pos
    WHERE  a1.testrun = 1
      AND  a2.testrun = 2


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Tariq Hasan Saturday, April 18, 2020 4:43 PM
    Saturday, April 18, 2020 9:48 AM

All replies

  • Can you explain how you arrive at the expected results? I'm afraid, I don't see much of a connection. If the data says that Vehicle 2 is at postition 24.52 after two seconds sharp, why should the output say 20 meters?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 15, 2020 9:53 PM
  • @Erland.. It is not Vehicle 2 rather it is Vehicle 1 is at position 24.52 after two seconds sharp. Vehicles position at 24.52 m should show against the row of 30 meter . Vehicle position between 20.01 meter to 30.00 meter should fall within this row. I want get the average speed of all vehicles in each 1 second within each 10 meter span along the whole road. Thanks for your query. Let me know if you have any more query...I am just stuck up here to find out the solution.  

    • Edited by Tariq Hasan Thursday, April 16, 2020 5:20 PM Corection
    • Marked as answer by Tariq Hasan Friday, April 17, 2020 5:23 PM
    • Unmarked as answer by Tariq Hasan Friday, April 17, 2020 5:23 PM
    Thursday, April 16, 2020 4:15 PM
  • This could be what you are looking for:

    SELECT ceiling(Time), ceiling(Pos/10)*10, Avg(Speed)
    FROM   #speed
    WHERE  Pos > 0
    GROUP  BY ceiling(Time), ceiling(Pos/10)*10
    ORDER  BY ceiling(Time), ceiling(Pos/10)*10


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Tariq Hasan Friday, April 17, 2020 8:37 AM
    • Unmarked as answer by Tariq Hasan Friday, April 17, 2020 5:23 PM
    Thursday, April 16, 2020 9:25 PM
  • Wow.. It completely solved my problems. Thank you Erland for ypur excellent support.

    Thanks again.

    Tariq

    Friday, April 17, 2020 8:37 AM
  • @Erland.. i have an extended query on the above issue. Suppose i have two tables with same data structures. With the same query i got two expected result as shown above. Now i want to get the Average of difference of speed corresponding to same time and same position between two result output. Suppose at 3 second at position 10 m the speed difference between result output of Table-1 and Table-2. Similar ways when get the difference of all the speed corresponding to similar time and position then i want to determine the average of speed difference. The final result will be a single numeric speed difference value. Your kind assistance will highly appreciated. Regards.  
    Friday, April 17, 2020 5:21 PM
  • Sorry, it is almost impossible to help with such problems from text only. I  could read your text multiple times and still interpret it different from what you intended.

    The preferred way to get help with problem of this type is to post CREATE TABLE statements for your tables, INSERT statements with sample data, and the desired result given the sample data. That resolves many ambbiguities in the description and makes it easy to copy and paste into a query window to develop a tested solution.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, April 17, 2020 9:19 PM
  • Sorry, probably i made it critical in making you understand. Suppose with you proposed code i got result like this one from Table-1:

           Sim Time(sec)    Position (m)    Avg Speed (km/hr)
               1.00            10.00            29.05
               1.00            20.00            21.15 
               2.00            10.00            26.55 
               2.00            20.00            28.39
               2.00            30.00            25.99
               3.00            10.00            25.89
               3.00            20.00            28.52 
               3.00            30.00            26.82
               3.00            40.00            27.21
               3.00            50.00            31.01

    And from Table-2 i get this result:

          Sim Time(sec)     Position (m)    Avg Speed (km/hr)
               1.00            10.00            27.12
               1.00            20.00            24.34 
               2.00            10.00            23.35 
               2.00            20.00            25.59
               2.00            30.00            23.49
               2.00            40.00            28.41
               3.00            10.00            21.59
               3.00            20.00            24.25 
               3.00            30.00            24.32
               3.00            40.00            29.54
               3.00            50.00            33.41
               3.00            60.00            28.67
               3.00            70.00            31.23
    The Speed Difference between both the table corresponding to similar time and similar position is :

    Sim Time (sec) Position (m) Absolute Speed Diff

    1 10 1.93 1 20 3.19 2 10 3.2 2 20 2.8 2 30 2.5 2 40 3 10 4.3 3 20 4.27 3 30 2.5 3 40 2.33 3 50 2.4 3 60 3 70

    Finally i want to get the sum of abs speed difference which is 29.42

      

    • Edited by Tariq Hasan Saturday, April 18, 2020 7:06 AM
    Saturday, April 18, 2020 7:04 AM
  • For your convenient i have posted the code and data of Table-1. The data of Table-2 is not prepared yet. Fictitious result of Table-2 may be taken as shown in my previous reply.

      
    CREATE TABLE SimRecords (
        [Sim Time (sec)] DEC(9,2),
    	[Veh No] INT,
        [Position (m)] DEC(9,2),
        [Speed (km/hr)] DEC(9,2)
    )
    
    INSERT INTO SimRecords VALUES 
    (0.20,1,0.01,0.00),
    (0.40,1,2.74,34.56),
    (0.60,1,5.46,23.45), 
    (0.60,2,2.20,45.12),
    (0.60,3,1.30,25.12),
    (0.80,1,8.18,34.12),
    (0.80,2,5.67,20.19),
    (0.80,3,4.65,34.54),
    (1.00,1,10.91,21.15),
    (1.00,2,9.14,19.28),
    (1.00,3,8.00,25.12),
    (1.20,1,13.63,34.12),
    (1.20,2,12.61,28.45),
    (1.20,3,11.35,34.12),
    (1.20,4,0.87,32.11),
    (1.40,1,16.35,21.18), 
    (1.40,2,16.07,23.12),
    (1.40,3,14.70,24.34),
    (1.40,4,4.44,27.76),
    (1.60,1,19.08,34.12),
    (1.60,2,19.54,25.87 ),
    (1.60,3,18.05,20.12),
    (1.60,4,8.00,34.11),
    (1.60,5,1.65,21.11),
    (1.80,1,21.80,29.34),
    (1.80,2,23.01,25.98),
    (1.80,3,21.40,22.31),
    (1.80,4,11.57,33.98),        
    (1.80,5,4.37,21.87),
    (2.00,1,24.52,29.34),
    (2.00,2,26.48,26.01),
    (2.00,3,24.75,22.98), 
    (2.00,4,15.14,32.91),
    (2.00,5,7.08,22.34),
    (2.20,1,27.25,28.67),  
    (2.20,2,29.95,25.89),
    (2.20,3,28.10,23.01),  
    (2.20,4,18.70,31.23),
    (2.20,5,9.79,23.03), 
    (2.40,1,29.97,27.67),  
    (2.40,2,33.42,26.56),     
    (2.40,3,31.44,24.12),
    (2.40,4,22.27,30.54),
    (2.40,5,12.48,24.01),
    (2.40,6,1.35,32.76),
    (2.60,1,32.69,23.34),
    (2.60,2,36.88,34.32),
    (2.60,3,34.79,32.32),
    (2.60,4,25.83,26.23),
    (2.60,5,15.16,34.21), 
    (2.60,6,5.20,23.21),
    (2.80,1,35.42,26.21),
    (2.80,2,40.35,31.01),
    (2.80,3,38.14,23.45),
    (2.80,4,29.40,25.74),
    (2.80,5,17.83,24.61),
    (2.80,6,9.05,28.65),
    (2.80,7,3.07,21.34),
    (2.80,8,2.10,26.34),
    (3.00,1,38.14,27.32)

    Saturday, April 18, 2020 8:07 AM
  • Sounds like this is what you are looking for:

    ; WITH tbl1agg AS (
       SELECT ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl1
       WHERE  Pos > 0
       GROUP  BY ceiling(Time), ceiling(Pos/10)*10
    ), tbl2agg AS (
       SELECT ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl2
       WHERE  Pos > 0
       GROUP  BY ceiling(Time), ceiling(Pos/10)*10
    )
    SELECT SUM(abs(t1.avgspeed - t2.avgspeed))
    FROM   tbl1agg t1
    JOIN   tbl2agg t2 ON t1.Time = t2.Time
                     AND t1.Pos  = t2.Pos

    I like to add that it sounds funny that you would have two tables. A better design would be to have a single table with one number column called "testrun" or whatever the two tables signify. This permits for a somewhat shorter solution:

    ; WITH aggr AS (
       SELECT testno, ceiling(Time) Time, ceiling(Pos/10)*10 AS pos, Avg(Speed) AS avgspeed
       FROM   tbl
       WHERE  Pos > 0
       GROUP  BY testno, ceiling(Time), ceiling(Pos/10)*10
    ) SELECT SUM(abs(a1.avgspeed - a2.avgspeed))
    FROM   aggr a1
    JOIN   aggr a2 ON a1.Time = a2.Time
                  AND a1.Pos  = a2.Pos
    WHERE  a1.testrun = 1
      AND  a2.testrun = 2


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Tariq Hasan Saturday, April 18, 2020 4:43 PM
    Saturday, April 18, 2020 9:48 AM
  • Thanks a lot Erland. Exactly that i wanted. I do not know where you are from.. Stay fine from CORONA 
    Saturday, April 18, 2020 4:43 PM