Hourly Average

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Compliance
    #1

    Hourly Average

    Apologies for the simplicity of the question, but it reflects my
    capabilities! I have the following sample fields coming from different
    tables:

    Location
    TimeDate (timestamp)
    Data

    I need to return the average of Data per Location per HOUR.

    Thanks.

    Tags: None
    • Roy Harvey
      #2
      Re: Hourly Average

      I believe this will do it.

      SELECT Location,
      dateadd(hour,da tediff(hour,0,T imeDate),0) as Hourly,
      avg(Data) as AvgData
      FROM .....
      GROUP BY
      Location,
      dateadd(hour,da tediff(hour,0,T imeDate),0)

      Roy Harvey
      Beacon Falls, CT

      On 17 Aug 2006 09:31:32 -0700, "Compliance " <comcontrol@aol .com>
      wrote:
      >Apologies for the simplicity of the question, but it reflects my
      >capabilities ! I have the following sample fields coming from different
      >tables:
      >
      >Location
      >TimeDate (timestamp)
      >Data
      >
      >I need to return the average of Data per Location per HOUR.
      >
      >Thanks.

        Comment

        • Compliance
          #3
          Re: Hourly Average

          Roy:

          Thanks for the reply! I have run this and am getting a syntax error on
          the second line which says there is incorrect syntax near the '(' . I
          played with spacing but could not resolve. Do you have any tips?
          Also, is it inappropriate

          Roy Harvey wrote:
          I believe this will do it.
          >
          SELECT Location,
          dateadd(hour,da tediff(hour,0,T imeDate),0) as Hourly,
          avg(Data) as AvgData
          FROM .....
          GROUP BY
          Location,
          dateadd(hour,da tediff(hour,0,T imeDate),0)
          >
          Roy Harvey
          Beacon Falls, CT
          >
          On 17 Aug 2006 09:31:32 -0700, "Compliance " <comcontrol@aol .com>
          wrote:
          >
          Apologies for the simplicity of the question, but it reflects my
          capabilities! I have the following sample fields coming from different
          tables:

          Location
          TimeDate (timestamp)
          Data

          I need to return the average of Data per Location per HOUR.

          Thanks.

            Comment

            • Roy Harvey
              #4
              Re: Hourly Average

              I checked the syntax and what I posted seems clean, other than the
              lack of the FROM clause. I adapted it to a meaningess example using
              an actual table:

              SELECT id,
              dateadd(hour,da tediff(hour,0,c rdate),0) as Hourly,
              avg(schema_ver) as AvgData
              FROM sysobjects
              GROUP BY
              id,
              dateadd(hour,da tediff(hour,0,c rdate),0)

              If you can't find the problem, past the exact code that is not
              executing into your reply.

              Roy Harvey
              Beacon Falls, CT

              On 17 Aug 2006 11:24:46 -0700, "Compliance " <comcontrol@aol .com>
              wrote:
              >Roy:
              >
              >Thanks for the reply! I have run this and am getting a syntax error on
              >the second line which says there is incorrect syntax near the '(' . I
              >played with spacing but could not resolve. Do you have any tips?
              >Also, is it inappropriate
              >
              >Roy Harvey wrote:
              >I believe this will do it.
              >>
              >SELECT Location,
              > dateadd(hour,da tediff(hour,0,T imeDate),0) as Hourly,
              > avg(Data) as AvgData
              > FROM .....
              > GROUP BY
              > Location,
              > dateadd(hour,da tediff(hour,0,T imeDate),0)
              >>
              >Roy Harvey
              >Beacon Falls, CT
              >>
              >On 17 Aug 2006 09:31:32 -0700, "Compliance " <comcontrol@aol .com>
              >wrote:
              >>
              >Apologies for the simplicity of the question, but it reflects my
              >capabilities ! I have the following sample fields coming from different
              >tables:
              >
              >Location
              >TimeDate (timestamp)
              >Data
              >
              >I need to return the average of Data per Location per HOUR.
              >
              >Thanks.

                Comment

                • Roy Harvey
                  #5
                  Re: Hourly Average

                  On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
                  SELECT tblRecord.HistA t
                  dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                  On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <comcontrol@aol .com>
                  wrote:
                  SELECT tblRecord.HistA t
                  dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                  There is a comma missing at the end of the first line.

                  Roy Harvey
                  Beacon Falls, CT

                    Comment

                    • Compliance
                      #6
                      Re: Hourly Average

                      My mistake...thank s. Now I am getting the following back, but its not
                      quite correct. See, unless I place the DateTime field in the GROUPBY I
                      get an error:
                      >[Error] Script lines: 1-9 --------------------------
                      Column 'Date/Time' is invalid in the select list because it is not
                      contained in either an aggregate function or the GROUP BY clause. 
                      >
                      So when my data comes back i get the actual TimeDate and the new
                      Hourly, but not the summarized hourly average (AvgData) alone:

                      DateTime Hourly AvgData
                      Name
                      --------------------- ---------------------
                      ------------------ ------------
                      7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
                      7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
                      7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName


                      Thanks again.

                      Roy Harvey wrote:
                      On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
                      >
                      SELECT tblRecord.HistA t
                      dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                      On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <comcontrol@aol .com>
                      wrote:
                      >
                      SELECT tblRecord.HistA t
                      dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                      >
                      There is a comma missing at the end of the first line.

                      Roy Harvey
                      Beacon Falls, CT

                        Comment

                        • Compliance
                          #7
                          Re: Hourly Average

                          Got it working now...I was retuening the DateTime data in the Select so
                          it was just giving me what I asked for.

                          Thanks Very Much Roy.


                          Compliance wrote:
                          My mistake...thank s. Now I am getting the following back, but its not
                          quite correct. See, unless I place the DateTime field in the GROUPBY I
                          get an error:
                          >
                          [Error] Script lines: 1-9 --------------------------
                          Column 'Date/Time' is invalid in the select list because it is not
                          contained in either an aggregate function or the GROUP BY clause. 
                          >
                          So when my data comes back i get the actual TimeDate and the new
                          Hourly, but not the summarized hourly average (AvgData) alone:
                          >
                          DateTime Hourly AvgData
                          Name
                          --------------------- ---------------------
                          ------------------ ------------
                          7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
                          7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
                          7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
                          >
                          >
                          Thanks again.
                          >
                          Roy Harvey wrote:
                          On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
                          SELECT tblRecord.HistA t
                          dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                          On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <comcontrol@aol .com>
                          wrote:
                          SELECT tblRecord.HistA t
                          dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                          There is a comma missing at the end of the first line.

                          Roy Harvey
                          Beacon Falls, CT

                            Comment

                            • Compliance
                              #8
                              Re: Hourly Average

                              Got it working now...I was retuening the DateTime data in the Select so
                              it was just giving me what I asked for.

                              Thanks Very Much Roy.


                              Compliance wrote:
                              My mistake...thank s. Now I am getting the following back, but its not
                              quite correct. See, unless I place the DateTime field in the GROUPBY I
                              get an error:
                              >
                              [Error] Script lines: 1-9 --------------------------
                              Column 'Date/Time' is invalid in the select list because it is not
                              contained in either an aggregate function or the GROUP BY clause. 
                              >
                              So when my data comes back i get the actual TimeDate and the new
                              Hourly, but not the summarized hourly average (AvgData) alone:
                              >
                              DateTime Hourly AvgData
                              Name
                              --------------------- ---------------------
                              ------------------ ------------
                              7/25/2006 9:30:06 PM 7/25/2006 9:00:00 PM 82.80000 1Name
                              7/25/2006 9:32:03 PM 7/25/2006 9:00:00 PM 82.40000 1Name
                              7/25/2006 9:34:09 PM 7/25/2006 9:00:00 PM 82.09999 1TName
                              >
                              >
                              Thanks again.
                              >
                              Roy Harvey wrote:
                              On Thu, 17 Aug 2006 12:02:49 -0700, you wrote:
                              SELECT tblRecord.HistA t
                              dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                              On Thu, 17 Aug 2006 12:02:49 -0700, "Compliance " <comcontrol@aol .com>
                              wrote:
                              SELECT tblRecord.HistA t
                              dateadd(hour,da tediff(hour,0,H istAt),0) as Hourly,
                              There is a comma missing at the end of the first line.

                              Roy Harvey
                              Beacon Falls, CT

                                Comment

                                Previous template Next
                                Working...