I'm relatively new to ColdFusion so please bear with me. I'm sure there's probably a simple solution to this but I've literally spent hours online trying to find something but to no avail.

Anyway, all I'm trying to do is query a query using the count function.


<cfQuery  name= "UserInfo">

Select Name, State, Phone, count <tblUser.Name> as Count
from TblUser
group by State


Now what I need to do is only return the results from States that contain more than 50 records. I simplified the example but that's pretty much the gist of the problem I'm having.

Thanks for any help you can provide!

9 Years
Discussion Span
Last Post by lespaul00

<cfQuery name= "UserInfo">

Select Name, State, Phone, count <tblUser.Name> as Count
from TblUser
group by State


First, make sure you define a datasource for your query:

<cfQuery  name= "UserInfo" datasource="YOURDSN">

next, you'll want to create a WHERE statement. If you first create a query to COUNT your records in your table:

<cfquery name="userinfo" datasource"dsn">
SELECT count(Name) AS number_records
FROM tblUser

Then, let's set this value (the number of records) to a variable for simplicity:

<cfoutput query="userinfo">
<cfset x = userinfo.number_records#>

Now, let's create a query for your results. Here, we've established a variable with the criterion you need - the number of records constraint. So, let's perform the following:

<cfQuery  name= "UserInfo2">

Select Name, State, Phone
from TblUser
WHERE #userinfo.number_records# > '50'
group by State

Then, display your query results, and you have what you need. The reason I recommend separating it, is because it breaks down your logic. This way, you can easily follow what you're doing (especially if you're a beginner).

I haven't tested the code above, so let me know if you have bugs or further questions.

- Nick

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.