Hi Les, You could use ListValueCountNoCase if there is some reason for
not letting the dbms do this aggregation for you. On the whole the
database engine is better (i.e. faster) at this kind of data
manipulation than CF is.
What I would suggest is that your select gets the country and count of
Given a small Access database containing:
ID, PA_COUNTRY, PA_CATEGORY
cfquery name=PA_CT datasource=phoenixart
SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY
FROM papers
GROUP BY PA_CATEGORY
/cfquery
I get the following error:
You tried to execute a query that does not include the specified
you need to add the other columns in the group by clause...
cfquery name=PA_CT datasource=phoenixart
SELECT
DISTINCT PA_COUNTRY,
ID,
PA_CATEGORY
FROM
papers
GROUP BY
PA_CATEGORY,
ID,
PA_COUNTRY
/cfquery
That should work...
Mike
- Original Message -
cfquery name=PA_CT datasource=phoenixart
SELECT DISTINCT PA_COUNTRY, ID, PA_CATEGORY
FROM papers
GROUP BY PA_CATEGORY, ID, PA_COUNTRY
/cfquery
will work.
I think that all of the columns in a select distinct need to be in the
groupby also if I remember correctly.
-Original Message-
Just as a little more detail, since you were grouping your query by one column, the db
didn't know what to do with the other columns, so like the others said, adding the
other columns to the 'group by' fixes your error.
But in other cases, you may need to use aggregate functions on fields that
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 =
De-dupe the list then count it
:)
-Jack
-Original Message-
From: Les Mizzell [mailto:[EMAIL PROTECTED]
Sent: Monday, August 04, 2003 10:33 AM
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
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
::: CFSET egypt_Count =
ListValueCountNoCase(ValueList(PA_CT.PA_COUNTRY),egypt)
:: De-dupe the list then count it
How you you do that, exactly?
I need to be able to use #InsertNameOfCountryHere_Count# anywhere on the
page as needed - for each of some 30 different countries
9 matches
Mail list logo
