your sum is being GROUPed by each individual donor. it might be more
advantageous to calculate it yourself while looping through the "individual
donors" query.
ex: query
<cfquery name="GetReportInfo" datasource="tjf" dbtype="ODBC">
SELECT
Donors.ID, Donors.Fname, Donors.MI, Donors.Lname, Donations.DateDonated,
Donations.AmountDonated,
FROM Donors, Donations
WHERE Donors.ID = Donations.DonorID AND Donations.DateDonated Between
#CreateODBCDate(StartDate)# and #CreateODBCDate(EndDate)#
ORDER BY Donors.ID, Donors.Fname, Donors.MI, Donors.Lname,
Donations.DateDonated
</cfquery>
then in your output...
<CFSET numdonors = 0>
<CFSET totaldonations = 0>
<cfoutput query="GetReportInfo">
<CFSET numdonors = numdonors + 1>
<CFSET totaldonations = totaldonations + getReportInfo.amountdonated>
</CFOUTPUT>
your totals will be in these two variables to display at your leisure.
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-----Original Message-----
From: Chris Montgomery [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 21, 2001 5:53 PM
To: CF-Talk
Subject: A little help with aggregate query, please?
Howdy,
I have an aggregate query that's kicking my butt. Its purpose is to retrieve
a simple report of donations by donors within a given date range. Most
everything works except for the Count and Sum functions. I'm not sure this
can be done in one query or not. Database is MS Access.
What I'm trying to do: display individual donations (name, amount and date
they donated) and then have a total for the number of people who donated
during this period and the total amount of their donations.
Here's what a simple report should look like:
Period: 1/1/2001 to 3/1/2001
Donor Amount Donated Date Donated
James C Montgomery $1.00 02/21/01
Joe A Blow $1.00 02/21/01
John C Doe $1.00 02/21/01
Total Donors This Period: 3
Total Donated This Period: $3.00
But here's what I'm getting:
Period: 1/1/2001 to 3/1/2001
Donor Amount Donated Date Donated
James C Montgomery $1.00 02/21/01
Joe A Blow $1.00 02/21/01
John C Doe $1.00 02/21/01
Total Donors This Period: 1
Total Donated This Period: $1.00
Notice the totals aren't computing properly.
Here's my code:
<cfset StartDate = StartMonth & "/" & StartDay & "/" & StartYear>
<cfset EndDate = EndMonth & "/" & EndDay & "/" & EndYear>
<cfquery name="GetReportInfo" datasource="tjf" dbtype="ODBC">
SELECT Donors.ID, Donors.Fname, Donors.MI, Donors.Lname,
Donations.DateDonated, Donations.AmountDonated, Count(Donors.ID) AS
DonorCount, Sum(Donations.AmountDonated) AS TotalAmount
FROM Donors, Donations
WHERE Donors.ID = Donations.DonorID AND Donations.DateDonated Between
#CreateODBCDate(StartDate)# and #CreateODBCDate(EndDate)#
GROUP BY Donors.ID, Donors.Fname, Donors.MI, Donors.Lname,
Donations.DateDonated, Donations.AmountDonated
</cfquery>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Donation Report</title>
</head>
<body>
<cfif GetReportInfo.recordcount>
<h3 align="center">Period: <cfoutput>#startdate# to
#enddate#</cfoutput></h3>
<table border="0" cellspacing="2" cellpadding="2" align="center">
<tr align="left">
<th>Donor</th>
<th>Amount Donated</th>
<th>Date Donated</th>
</tr>
<cfoutput query="GetReportInfo">
<tr>
<td>#fname# #mi# #lname#</td>
<td>#DollarFormat(amountdonated)#</td>
<td>#DateFormat(datedonated, "mm/dd/yy")#</td>
</tr>
</cfoutput>
<tr>
<td colspan="3"><hr size="1"></td>
</tr>
<tr>
<td></td>
<td>Total Donors This Period:
<cfoutput>#GetReportInfo.donorcount#</cfoutput></td>
<td>Total Donated This Period:
<cfoutput>#DollarFormat(GetReportInfo.totalamount)#</cfoutput></td>
</tr>
</table>
<cfelse>
<p>There were no donations during this period.</p>
</cfif>
</body>
</html>
If anyone can help me figure out how to fix the query I would greatly
appreciate it.
Chris Montgomery [EMAIL PROTECTED]
Web Development & Consulting http://www.astutia.com
Allaire Consulting Partner
210-490-3249/888-745-7603 Fax 210-490-4692
AIM: astutiaweb; ICQ: 7381282; Firetalk: Ag78
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
