5 Replies Latest reply on May 12, 2006 9:47 AM by Kronin555

    avg, max, and min calculated from form data

    muellertj Level 1
      I have two tables. One with raw data and one with summary data. There is a form that the raw column data is input into then on submit I use a cfloop function to get the data into the same column in the raw data table. I can't seem to figure out an easy way to calculate the max, min, and average values for the data that is put into the raw data table that can be put into the summary data table. The data is customized by location and date.

      This is the code for getting the raw data into the table.

      <CFLOOP INDEX="onerow" FROM="1" TO="#form.numrows#">
      <CFQUERY NAME="insertrawdata" DATASOURCE="test">
      INSERT INTO test.dbo.rawdata (locationid, date, data)
      VALUES ('#form.locaitonid#', #DateValue#, '#Form["data" & onerow]#'')
      </CFQUERY>
      </CFLOOP>
        • 1. Re: avg, max, and min calculated from form data
          dempster Level 1
          Just initialize some variables before you enter the loop. Then you can increment a total variable and check for max and min values. When you exit the loop, divide the total by the count for the average and you'll have the info you need.
          • 2. Re: avg, max, and min calculated from form data
            Kronin555 Level 1
            insert into summarytable (locationid,date,max,min,avg)
            values
            ('#form.locationid#', #DateValue#,
            select max(data) from test.dbo.rawdata where locationid = '#form.locationid#' and date = #datevalue#,
            select min(data) from test.dbo.rawdata where locationid = '#form.locationid#' and date = #datevalue#,
            select avg(data) from test.dbo.rawdata where locationid = '#form.locationid#' and date = #datevalue#)
            • 3. Re: avg, max, and min calculated from form data
              muellertj Level 1
              When I try:

              <CFQUERY NAME="summarydata" DATASOURCE="test">
              INSERT INTO test.dbo.summarydata (locationid, date, max, min, avg)
              VALUES ('#form.locaitonid#', #DateValue#,
              SELECT MAX(data) FROM test.dbo.rawdata WHERE locationid = '#form.locationid#' AND date= #DateValue#,
              SELECT MIN(data) FROM test.dbo.rawdata WHERE locationid = '#form.locationid#' AND date= #DateValue#,
              SELECT AVG(data) FROM test.dbo.rawdata WHERE locationid = '#form.locationid#' AND date= #DateValue#)
              </CFQUERY>

              I get this error message:
              Incorrect syntax near the keyword 'SELECT'.
              • 4. Re: avg, max, and min calculated from form data
                Dan Bracuk Level 5
                This does the same thing as Kronin555's query, but will probably run faster.

                insert into summarytable (locationid,date,max,min,avg)
                select
                locationid, date, max(data), min(data), avg(data)
                from test.dbo.rawdata where locationid = '#form.locationid#' and date = #datevalue#
                group by locationid, date

                • 5. avg, max, and min calculated from form data
                  Kronin555 Level 1
                  OK, well, do it in 2 queries then...

                  <cfquery name="getSummaryData" datasource="test">
                  select max(data) as max,min(data) as min,avg(data) as avg from test.dbo.rawdata where locationid = '#form.locationid#' and date = #datevalue# group by locationid,date
                  </cfquery>

                  <cfquery name="insertSummaryData" datasource="test">
                  insert into test.dbo.summarydata (locationid,date,max,min,avg)
                  values ('#form.locationid#',#datevalue#,
                  #getSummaryData.max#,#getSummaryData.min#,#getSummaryData.avg#)
                  </cfquery>