I don't think you need DISTINCT at all if you are using GROUP BY. GROUP BY will group all the matching rate values together automatically, so the result will be all distinct values. Try something like (not tested)
SELECT Count(rate) as ratecount, rate FROM myrates etc. GROUP BY rate Dump that and see if you're any closer to what you want. -- Josh ----- Original Message ----- From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> To: "CF-Talk" <cf-talk@houseoffusion.com> Sent: Tuesday, April 03, 2007 2:09 PM Subject: Re: SQL Help - Answered > 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> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>> >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Macromedia ColdFusion MX7 Upgrade to MX7 & experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274483 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
