Sum 5 minute data by hour

Sum 5 minute data by hour

• minkee.s

  I figured it out after searching through the T-SQL online language reference, which is pretty nice I must say...if you know what your looking for or get lucky like I did.  Anyways, here's the query:

  SELECT CAST(CAST(CONVERT(INT,LEFT(RIGHT('0000'+CONVERT(NVARCHAR(4),(CASE Interval

  WHEN '2400' THEN '0'

  ELSE Interval END)),4),2)) AS VARCHAR(4))+'00' AS INT),

  AVG(Data) AS AvgData

  FROM tbTable

  GROUP BY CAST(CAST(CONVERT(INT,LEFT(RIGHT('0000'+CONVERT(NVARCHAR(4),(CASE Interval

  WHEN '2400' THEN '0'

  ELSE Interval END)),4),2)) AS VARCHAR(4))+'00' AS INT)

  ORDER BY Interval DESC

  This is what I ended up with.  This also adds the '00' to get my intervals back like they were originally.  The CASE() was used to change the '2400' interval to '0'.  Thanks for the help...btw, how do people post code so cleanly   I always get the spaces between my code when I edit it after pasting...as in the above case.

  
  

• mtm81

  Change 24 to 0.
  

• haguna99

  I have no control over the data in the tables, except for querying the data, but 1-24 would make sense if I could do it this way...
  

• gracias

  Can you try this:

  SELECT CONVERT(INTEGER,Left(RIGHT('0000'+CONVERT(Nvarchar(4),col_minutesInterval),4),2)), SUM(col_value) AS HourSum

  FROM hourMinutesValues

  GROUP BY CONVERT(INTEGER,Left(RIGHT('0000'+CONVERT(Nvarchar(4),col_minutesInterval),4),2))

  AND

  SELECT CONVERT(INTEGER,Left(RIGHT('0000'+CONVERT(Nvarchar(4),col_minutesInterval),4),2)), AVG(col_value) AS HourAvg

  FROM hourMinutesValues

  GROUP BY CONVERT(INTEGER,Left(RIGHT('0000'+CONVERT(Nvarchar(4),col_minutesInterval),4),2))

  
  

• shmulik_segal

  I think the range should be 0 to 2355( you cannot go to 2400, which is the same as 0.). If you do, you will have 25 hours in the result.

  
  

• Steven_Cahill

  I have both...(see screenshot)
  

• SouLDragooN

  I don't have your dataset, but you should have either 0 or 24 to avoid this problem. They are the same point of time.
  

• Will Merydith

  Could build a sample table with some sample data and provide a script for us to use That would make it easier to work with. Also provide the output you are expecting.
  
  

• CBuilder

  I see what's happening...the 100-155 interval contains 12 values but the 5-55 contains 11 values with the 2400 interval by itself.  The 24 interval should be added to the 0 sum to make either a 0 or 24 sum value.  Any ideas on how to do this
  

• dermothogan

  The only problem with this is that I get 25 intervals since I get a 0 interval sum as well as a 24 interval sum. Any idea on how to fix this
  
• Sum 5 minute data by hour