<!--- 
 Filename: ExpenseReport2.cfm
 Created by: Nate Weiss (NMW)
 Purpose: Demonstrates use of SQL aggregate functions
--->

<!--- Retrieve summarized expense data from database --->
<cfquery name="getExp" datasource="#APPLICATION.dataSource#">
  SELECT 
  f.FilmID, f.MovieTitle,
  SUM(ExpenseAmount) AS ExpenseSum
  FROM Expenses e, Films f
  WHERE e.FilmID = f.FilmID
  GROUP BY f.FilmID, f.MovieTitle
  ORDER BY f.MovieTitle
</cfquery>

<html>
<head><title>Film Expenses</title></head>
<body>
<h2>Expense Overview</h2>

<!--- Output film-level expense summary --->
<cftable query="getExp" htmltable="Yes" border="Yes" colheaders="Yes">

 <cfcol header="Film Title" text="#MovieTitle#">
 <cfcol header="Amount" text="#lsCurrencyFormat(ExpenseSum)#" align="right">

</cftable>

</body>
</html>
