Thanks for all the help so far I know I'm almost there
I figured out that all the items in the select need to be in the group by
clause, however what is causing the problem is the date. When I run the
query without the date it runs fine (actually I had to remove the FullName
and break it into pat_First, Pat_Last) but I assume the GROUP BY is wanting
everything the same before it adds them together. Well the date is the same
but the time is different causing the entries to not match exactly and
therefore will not add them together
New Query is:
<cfquery name="rsGetPayments" datasource="#application.dsn#"
username="#application.dsnUsername#" password="#application.dsnPassword#">
SELECT sum(tbl_HCFA_Purchase_Transactions.pur_Amount) AS Total,
tbl_HCFA_Purchase_Transactions.pur_Date_Purchased,
tbl_HCFA_Purchase_Transactions.Check_Num,
tbl_Patients.Pat_First, tbl_Patients.Pat_Last
FROM tbl_HCFA_Purchase_Transactions, tbl_Patients
WHERE tbl_HCFA_Purchase_Transactions.PatientID =
tbl_Patients.PatientID
AND pur_Date_Purchased BETWEEN
#CreateODBCDateTime(cFullStartDate)# AND
#CreateODBCDateTime(cFullEndDate)#
GROUP BY Check_Num, pur_Date_Purchased, Pat_Last, Pat_First
ORDER BY Check_Num
</cfquery>
Output is:
ID, Check_Num, pat_First, pat_Last, pur_Date_Purchased, Total
4, 8356, Dolores, Cannon, 2007-09-04 09:20:38.0, 100.000
5, 8357, Joseph, Manzanares, 2007-09-04 09:19:18.0, 204.0000
6, 8357, Joseph, Manzanares, 2007-09-04 09:19:55.0, 204.0000
7, 8358, Caesar, Marquez, 2007-09-04 09:31:02.0, 950.0000
Records 5 and 6 should be added together but since the time stamp is 9:19:18
and 9:19:55 they are not equal and therefore the sum wont add them I tried
taking the date out of the group by statement and get:
You tried to execute a query that does not include the specified expression
'pur_Date_Purchased' as part of an aggregate function.
Travis Haley
-----Original Message-----
From: Azadi Saryev [mailto:[EMAIL PROTECTED]
Sent: Monday, October 01, 2007 2:56 AM
To: CF-Talk
Subject: Re: Adding Small sections of a query
it should make no difference how many records you have in your table.
Dale's code will return the results you want.
Azadi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade to ColdFusion 8 and integrate with Adobe Flex
http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:289869
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
