EasyCFM.COM ColdFusion Forums / Coding Help! / Query of Query; Counting records

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Query of Query; Counting records

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

4Dante
02-13-2008 @ 5:35 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 7
Joined: Feb 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:

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

dlackey
02-13-2008 @ 5:46 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

What are the error message you are receiving?  Are you getting results, not the results you expect, or other?  

At first glance, your UserInfo query may not be syntactingly correct (if that is a word).  Count may be a reserved word depending on the DB you are using so I did make some slight adjustments. This hasn't been tested but the idea is:

<cfquery name="test" datasource="#test.dsn#">
Select Name, State, Phone, count <tblUser.Name> as ttl_Count
from TblUser
group by State
</cfquery>
<cfquery name="count_test" dbtype="query">
select count(ttl_Count) as count_sub
from test
</cfquery>

Cheers,
Chip

4Dante
02-15-2008 @ 5:56 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 7
Joined: Feb 2008

Hello dlackey,

I was not getting an error message, per se, since I'm not sure how to implement a solution to this issue.

I used the "as Count" on another page, and it worked ok, but I changed it to StateCount for this page, just in case.

I got the following error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Column 'dbo.tblUser.Name' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


Help?

dlackey
02-18-2008 @ 9:09 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

The problem is you haven't finished telling your sql statement what to group next.  Do you mean Group By or Order By?

If you mean Group By, then complete the statement by adding the rest of your fields from your select statement and add that to your Group By statment as such:  Group By State, Name, Phone, count



Cheers,
Chip

This message was edited by dlackey on 2-18-08 @ 9:10 AM

4Dante
02-18-2008 @ 10:44 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 7
Joined: Feb 2008

Thanks for the reply Chip.

Ok, but what if I didn't want to group the list?  I want all the results to show, but only the records that fit the count criteria.

In other words, I want to see all the results where the # of records per state is greater than 50.

dlackey
02-18-2008 @ 10:58 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 935
Joined: Jun 2007

remove the
group by
and add
where count >= 50

Note:  Not sure what database you are using; You may have to create a view first to get count and then join the view with the table.  

Cheers,
Chip


Website Designed and Developed by Pablo Varando.