You need a group by in your query.. > SELECT count(DISTINCT rate) as rateCount, rate > FROM myrates > WHERE my_code = 385 and year = 2005 GROUP BY rate > ORDER BY rate
On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Why do the first two queries work and the last one fail? The only difference > is adding the comma and additional field to the SELECT statement. > > <cfquery datasource="myDSN" name="Rates"> > SELECT count(DISTINCT rate) as rateCount > FROM myrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > </cfquery> > > <cfquery datasource="myDSN" name="Rates"> > SELECT rate > FROM myrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > </cfquery> > > > FAIL > > <cfquery datasource="myDSN" name="Rates"> > SELECT count(DISTINCT rate) as rateCount, rate > FROM myrates > WHERE my_code = 385 and year = 2005 > ORDER BY rate > </cfquery> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274477 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
