Skip to main content
T
Tonyam
Inspiring
May 12, 2006
Answered

avg, max, and min calculated from form data

  • May 12, 2006
  • 4 replies
  • 583 views
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>
This topic has been closed for replies.
Correct answer Kronin555
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>

4 replies

K
Kronin555Correct answer
Participating Frequently
May 12, 2006
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>
D
Dan_Bracuk
Inspiring
May 12, 2006
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

K
Kronin555
Participating Frequently
May 12, 2006
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#)
T
TonyamAuthor
Inspiring
May 12, 2006
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'.
D
dempster
Inspiring
May 12, 2006
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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices