MACROMEDIA ColdFusion MX, Develop Manual

Page: 623 / 860

Charting data 623
2
Save the page as chartdata.cfm in myapps under the web root directory. For example, the
directory path on Windows might be C:\Inetpub\wwwroot\myapps.
3
Return to your browser and enter the following URL to view chartdata.cfm:
http://127.0.0.1/myapps/chartdata.cfm
The following figure appears:
Note: If a query contains two rows with the same value for the
itemColumn
, ColdFusion graphs the
last row in the query for that value. For the previous example, if the query contains two rows for the
Sales department, ColdFusion graphs the value for the last row in the query for Sales.
Reviewing the code
The following table describes the code and its function:
Code Description
<cfquery name="GetSalaries"
datasource="CompanyInfo">
SELECT Departmt.Dept_Name,
Employee.Salary
FROM Departmt, Employee
WHERE Departmt.Dept_ID =
Employee.Dept_ID
</cfquery>
Query the CompanyInfo database to get the
Dept_Name and Salary for each employee.
Because the Dept_Name is in the Departmt table
and the Salary is in the Employee table, you need a
table join in the WHERE clause. The raw results of
this query could be used elsewhere on the page.
<cfquery dbtype = "query"
name = "DeptSalaries">
SELECT
Dept_Name,
AVG(Salary) AS AvgByDept
FROM GetSalaries
GROUP BY Dept_Name
</cfquery>
Generate a new query from the GetSalaries query.
Use the AVG aggregating function to get statistical
data on the employees. Use the GROUP BY
statement to ensure that there is only one row for
each department.