ColdFusion

Query of Query - Using Count

Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Feb 2008

Posts: 1

Reputation: 4Dante is an unknown quantity at this point

Solved Threads: 0

4Dante

Offline

Newbie Poster

Query of Query - Using Count

Feb 13th, 2008

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:

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<cfQuery  name= "UserInfo">
 
Select Name, State, Phone, count <tblUser.Name> as Count
from TblUser
group by State
 
</query>
<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!

Last edited by peter_budo; Feb 13th, 2008 at 3:57 pm. Reason: Please use [code] tags to separate code from rest of the post

•

Join Date: Sep 2007

Posts: 5

Reputation: lespaul00 is an unknown quantity at this point

Solved Threads: 0

lespaul00

Offline

Newbie Poster

Re: Query of Query - Using Count

Feb 16th, 2008

•

<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:

Help with Code Tags

(Toggle Plain Text)

<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:

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<cfquery name="userinfo" datasource"dsn">
SELECT count(Name) AS number_records
FROM tblUser
</cfquery>
<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:

 Help with Code Tags 
 ColdFusion Syntax (Toggle Plain Text) 
<cfoutput query="userinfo">
<cfset x = userinfo.number_records#>
</cfoutput>
<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:

Help with Code Tags

(Toggle Plain Text)

<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

Last edited by lespaul00; Feb 16th, 2008 at 7:06 pm.