Listing Number of Results
Is there a way, without having to write separate queries for each instance, to list the number of records sorted by a specific field? For example, I've got a table of products listed by a number of countries. On one page, I'd like a summary: US 12 products found INDIA 23 products found ITALY 10 products found. There's close to 40 countries in the database. Certainly don't want to put 40 queries on one page just to count the number for each country! What's Best Practice here? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Listing Number of Results
need db layout and can-do, but truly, its going to involve a count(somthingtoCount) and group by statement at the end :) tw tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 9:57 AM To: CF-Talk Subject: Listing Number of Results Is there a way, without having to write separate queries for each instance, to list the number of records sorted by a specific field? For example, I've got a table of products listed by a number of countries. On one page, I'd like a summary: US 12 products found INDIA 23 products found ITALY 10 products found. There's close to 40 countries in the database. Certainly don't want to put 40 queries on one page just to count the number for each country! What's Best Practice here? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
RE: Listing Number of Results
Does the country appear in a field? If so, you could use something like this: CFSET US_Count = ListValueCountNoCase(ValueList(qryMyQuery.CountryField),US) CFSET INDIA_Count = ListValueCountNoCase(ValueList(qryMyQuery.CountryField),INDIA) ...etc... Chris -Original Message- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 9:57 AM To: CF-Talk Subject: Listing Number of Results Is there a way, without having to write separate queries for each instance, to list the number of records sorted by a specific field? For example, I've got a table of products listed by a number of countries. On one page, I'd like a summary: US 12 products found INDIA 23 products found ITALY 10 products found. There's close to 40 countries in the database. Certainly don't want to put 40 queries on one page just to count the number for each country! What's Best Practice here? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=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
RE: Listing Number of Results - One More Question
:: CFSET US_Count = ListValueCountNoCase(ValueList(qryMyQuery.CountryField),US) :: CFSET INDIA_Count = ListValueCountNoCase(ValueList(qryMyQuery.CountryField),INDIA) This works great! Thanks! I suddenly realized that I've got one further problem though... In the database, I've got (for example) two fields Country | Prod_Category I suddenly realize that I'm trying to count the number of distinct categories for each country, not the number of times each country appears... So, if the category is red (and there's several hundred catagories), how many red for the US, how many red for Nepal... A compound SQL query using DISTINCT ?? Not sure. Suggestions? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
