Getting an average UPDATED

• brian.cook

  Default port

  Points: 1484

  More actions

  May 25, 2018 at 11:15 am

  #1992015

  sgmunson - Friday, May 25, 2018 10:54 AM

  brian.cook - Friday, May 25, 2018 9:37 AM

  sestell1 - Friday, May 25, 2018 8:43 AM

  Hmm, you are grouping by the same columns you are averaging, so that probably isn't giving you what you want. 
  Also the OVER clause is only being applied to the last average of SSI3.  

  If you just want a straight average of the SSI columns by WEA, you could do this:

  
SELECT
WEA,
AVG(SSI1),
AVG(SSI2),
AVG(SSI3)
FROM RT_Group_Coverage
GROUP BY WEA


  Based on your initial post though, I thought you were looking to average only records in the 24 hours prior to each record, which is what this code does:

  
SELECT
  MainData.WEA,
  Max(MainData.SSI1) AS SSI1,
  Max(MainData.SSI2) AS SSI2,
  Max(MainData.SSI3) AS SSI3,
    Max(MainData.Date_Time) AS Date_Time,
  AVG(AvgData.SSI1) AS Average_SSI1,
  AVG(AvgData.SSI2) AS Average_SSI2,
  AVG(AvgData.SSI3) AS Average_SSI3
FROM
  RT_Group_Coverage MainData
Join
  RT_Group_Coverage AvgData
   ON
    AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
  MainData.WEA


  It joins each record to all records within 24 hours prior to the record's date_time, allowing them to then be averaged. 

  I was able to run that query against the sample table and data you provided (although I did have to change WEA to a BigInt in the table as your values were larger than an integer allows).
  Feel free to post your modified code so we can try and spot the syntax error.

  sestell1, thanks, I see in this one you added Max(MainData.Date_Time) AS Date_Time, now the query does run. It errors now with an Arithmetic overflow error converting expression to data type int.
  The WEA field is actually a FLOAT data type, by changing the Select statement to read;
  SELECT
  MainData.WEA AS BIGINT,
  Max(MainData.SSI1) AS SSI1,
  Max(MainData.SSI2) AS SSI2,
  Max(MainData.SSI3) AS SSI3,
  Max(MainData.Date_Time) AS Date_Time,
  AVG(AvgData.SSI1) AS Average_SSI1,
  AVG(AvgData.SSI2) AS Average_SSI2,
  AVG(AvgData.SSI3) AS Average_SSI3
FROM
  RT_Group_Coverage MainData
Join
  RT_Group_Coverage AvgData
    ON
    AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY
  MainData.WEA It executes correctly.

  You are correct in the initial assumptions, I was just trying to expand on what you had given me and see if I could make it work. 🙂 Thank you for explaining what I was getting. The education helps.

  I almost posted a lot earlier to indicate that the data you supplied for the WEA column exceeded the size of an Integer, so if you are going to avoid the float data type, here's how you do that:
  SELECT
    CONVERT(BIGINT, MainData.WEA)    AS WEA,
    MAX(MainData.SSI1)                AS SSI1,
    MAX(MainData.SSI2)                AS SSI2,
    MAX(MainData.SSI3)                AS SSI3,
    MAX(MainData.Date_Time)            AS Date_Time,
    AVG(AvgData.SSI1)                AS Average_SSI1,
    AVG(AvgData.SSI2)                AS Average_SSI2,
    AVG(AvgData.SSI3)                AS Average_SSI3
FROM RT_Group_Coverage AS MainData
    INNER JOIN RT_Group_Coverage AS AvgData
        ON MainData.WEA = AvgData.WEA
        AND AvgData.Date_Time BETWEEN DateAdd(hour, -24, MainData.Date_Time) AND MainData.Date_Time
GROUP BY CONVERT(BIGINT, MainData.WEA)
ORDER BY CONVERT(BIGINT, MainData.WEA);

  The way you have the query set up, it will remain a float data type in the result set, as you end up using the word BIGINT as a column alias rather than a data type.  Also please note that your join should probably include matching the WEA values.

  That looks better. Took 53 seconds to run thru this time. Resulting in 3756 rows. 

  Thank you for the help all of you.

  Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner