That was my original attempt, but results in the following error: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Dan =================== Previous Message Below =================== -----Original Message----- From: Michael T. Tangorre [mailto:[EMAIL PROTECTED] Sent: Sunday, August 24, 2003 10:00 AM To: CF-Talk Subject: RE: SQL aggregate question Try a "where" caluse instead of a having... SELECT ptssn, sum(arbalancedue) as sumar FROM ga_temp WHERE sum(arbalancedue) < 50 GROUP BY by ptssn Mike -----Original Message----- From: Dan O'Keefe [mailto:[EMAIL PROTECTED] Sent: Sunday, August 24, 2003 9:50 AM To: CF-Talk Subject: OT: SQL aggregate question Is what I am trying to do possible, and if it is, what am I doing wrong? This query: ----------- select ptssn, sum(arbalancedue) as sumar from ga_temp group by ptssn having sum(arbalancedue) < 50 Yields these results: --------------------- Ptssn sumAr 237451046 26.4800 255785250 25.2000 267582694 42.3200 294629611 42.3200 But what I really want is 1 query to return to me a count of 4 with a total of 136.32 This query returns no results: ------------------------------ select count(distinct ptssn), sum(arbalancedue) as sumar from ga_temp having sum(arbalancedue) < 50 Thanks, Dan ------------ Dan O'Keefe 954-501-3115 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
