Query of Query - Using Count

[4Dante]

Hello,

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.

Exm:

<cfQuery  name= "UserInfo">

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

</query>

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!

[lespaul00]

<cfQuery name= "UserInfo">

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

</query>

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
</cfquery>

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

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

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
</query>

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