removing the DISTINCT did the trick. Thank you both.
Josh Nathanson wrote: > 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:274507 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
