Query of Query - Using Count

•

What is DaniWeb IT Discussion Community?

You're currently browsing the ColdFusion section within the Web Development category of DaniWeb, a massive community of 426,800 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 1,910 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our ColdFusion advertiser: Programming Forums

Thread Tools Display Modes

Views: 1323 | Replies: 1

•

Join Date: Feb 2008

Posts: 1

Reputation: 4Dante is an unknown quantity at this point

Rep Power: 0

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

(Toggle Plain Text)

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

Rep Power: 0

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

(Toggle Plain Text)

<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

(Toggle Plain Text)

<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 6:06 pm.