Webmaster Forum / ColdFusion / Getting Started / January 2008

Hi here is your code i just instead of using a condition i used the condition
to add to a new sub query. It should work fine but i could not test it hope
this help.
:)

Enjoy!

<cfquery name="q_countries" datasource="mydbase">
SELECT UCASE(country) AS Country, COUNT(*) AS country_count
FROM clientinfo
WHERE country IS NOT NULL
GROUP BY country
ORDER BY country_count
</cfquery>
<cfset counter = 1>
<cfset c_countries = querynew('Country,country_count','VARCHAR,NUMERIC')>
<cfloop query="q_countries" >
    <cfif '#country_count#' GTE '5'>
        <cfset queryaddrow('c_countries','1')>
        <cfset querysetcell('c_countries','Country','#Country#','#counter#')>
        <cfset
querysetcell('c_countries','country_count','#country_count#','#counter#')>
        <cfset counter = #counter# + 1>
    </cfif>
</cfloop>
<!--- set the value if country count is greater than or equal to 5--->
<cfoutput query="c_countries">
<cfset country_count=country_count>
<br>#country_count#
</cfoutput>

<cfchart format="flash"
font="ArialUnicodeMs"
chartwidth="600"
chartheight="400"
seriesplacement="cluster"
title = "Website Visitors by Country"
pieSliceStyle="solid"
scalefrom="5"
show3d="true"
yAxistitle="%"
showLegend="yes">

<!---country count is not updated--->
<cfchartseries type="pie" query="c_countries"
itemcolumn="country" valuecolumn="country_count">
</cfchartseries>
</cfchart>

Chemical_One demonstrates a good practice. Data manipulation is best left to
your SQL database so work on eliminating the need for ColdFusion to manipulate
data like using CFIF, CFSET to convert data from a query before displaying.

Hi,

[Q]<cfoutput query="q_countries"><cfif country_count gte 5><cfset
country_count=country_count>
<br>#country_count#</cfif></cfoutput>[/Q]

You are setting a local variable to hold the value of a query column with the
same name. That generally is a bad idea, because, at the very least, it will be
confusing for any developer that reads this code.

Plus, after the loop, the local variable will hold the value of the last row
of the query column. Not really what you are looking for. ;-)

While  Chemical_One's solution will work, it adds a lot of overhead...
creating 2 queries where only 1 is needed, an extra loop, etc.

The best way would be to only select those records that match your criteria:

SELECT UCASE(country) AS Country, COUNT(country) AS country_count
FROM clientinfo
WHERE country IS NOT NULL
AND count(country)  >= 5
GROUP BY country
ORDER BY country_count

HTH,

Chris

thanks for the help everyone really appreciated