Maybe you need to exclude ID, something like <cfquery name="PA_CT" datasource="phoenixart"> SELECT PA_COUNTRY, count(distinct PA_CATEGORY) as category_count FROM papers GROUP BY PA_COUNTRY </cfquery>
This will count the distinct categories, grouped by country. If you still have problems, please post a sample of data as this will clarify the problem. Michael Traher Systems Manager ICLP (London) Tel: UK +44 (0) 20 8256 9072 Fax: UK +44 (0) 20 8681 0234 This e-mail may contain privileged and confidential information and/or copyright material and is intended for the use of the addressee only. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software and delete this e-mail from your computer system. You may not deliver, copy or disclose its contents to anyone else. Any unauthorised use may be unlawful. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of ICLP. -----Original Message----- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 04 August 2003 15:33 To: CF-Talk Subject: RE: What the heck is wrong with this Query? The query itself doesn't error out like this, but I'm not getting the needed results to "count" categories like I need: <cfquery name="PA_CT" datasource="phoenixart"> SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY FROM papers GROUP BY PA_COUNTRY, ID, PA_CATEGORY </cfquery> <CFSET egypt_Count = ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),"egypt")> This should give me the number of distinct "categories" for the distinct country, "egypt". However, it's counting each individual instance, not the unique ones: This is a "flat" database - so, I'm trying to group the countries, and then count the number of unique catagories for each country... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
