I'm going to have to look at something. I'm still not getting what I anticipated.
There are over 3700 records of which 1775 of them are distinct values for "RATE". However, I am only getting values of 1 for COUNT(DISTINCT rate) as rateCount. I was trying to find out how many records are there for each distinct value in the RATE field. I need to chart how many records there are for each value in RATE. Thanks for the help. Greg Morphis wrote: > No problem, if you want to know "why" take a look at aggregate > functions, which is what "count" is, as well as others.. > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > >> Thank you >> >> Greg Morphis wrote: >> >>> 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> >>>> >>>> >>>> >>>> >>>> >>>> >>> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274481 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
