Tim,
The query is working fine, the way you wrote it :) In order for the avg
function to work, your query needs to return 1 record. That way it can
average all rows together. Right now it's calculating the average of each
row which is the same as the value of each row. If you were to loop through
the query records, you would find that it returns the numbers in the same
pattern. You need to rewrite your query like so:
<CFQUERY NAME="qGetAverage" DATASOURCE="info_survey">
SELECT AVG(overall) AS OAvg, AVG(needs) as NAvg, AVG(tech) AS TAvg,
AVG(equipment) AS EAvg, AVG(software) AS SAvg, location
FROM main
WHERE (location = 'Atlanta')
GROUP BY location
</CFQUERY>
Marlon
-----Original Message-----
From: Tim [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 12, 2001 10:05 AM
To: CF-Talk
Subject: Aggregate functions (AVG())
I have a simple query that includes the AVG() function
to average some fields. The code is:
<CFQUERY NAME="qGetAverage" DATASOURCE="info_survey">
SELECT overall, AVG(overall) AS OAvg, needs,
AVG(needs) as NAvg, tech, AVG(tech) AS TAvg,
equipment, AVG(equipment) AS EAvg, software,
AVG(software) AS SAvg, location
FROM main
WHERE (location = 'Atlanta')
GROUP BY location, overall, needs, tech, equipment,
software
</CFQUERY>
<cfoutput>
Results for Atlanta; number of responses:
#qGetAtlanta.RecordCount#
<br>
Overall Rating of IT, #qGetAtlanta.OAvg#<br>
Overall rating of IT support, #qGetAtlanta.NAvg#<br>
Technical capability, #qGetAtlanta.tavg#<br>
Equipment availability, #qGetAlanta.eavg#<br>
Software availability, #qGetAlanta.savg#<br>
</cfoutput>
However, it is not outputting the average of each
field. It returns the correct number od records, 4,
but appears to just output the value in each field of
the first record.
The four records have field values of 2, 3, 1, 4, 5;
5, 4, 3, 5, 4; 2, 4, 3, 2, 1; and 5, 5, 5, 5, 5. The
output is returning values of 2, 3, 1, 4 and 5, the
values of the first record's fields. They should be
3.5, 4.0, 3.0, 4.0 and 3.75.
Any ideas?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
