can you provide a sample of your data and the way your table is designed (column name, type)? Thanks
On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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> > >>>> > >>>> > >>>> > >>>> > >>>> > >>>> > >>> > >> > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274482 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
