recordcount a group

[Guest]

If you have a query... and you output your results with groups...can you get a recordcount of how many records there are for each group? Much like you would do query.recordcount - can you do group.recordcount without having to do a mathematical approach such as doing <CFSET groupcount=groupcount +1>.

Example:

<query>

<cfoutput query="query" group="name">
#name#
<cfouptut query"query" group="city">
#city#
</cfoutput>
</cfoutput>

I want to know how many records are contained in each group without having to do a CFSET.

[Dan_Bracuk]

You could do it with query of queries, but somewhere along the line you are going to have to increment a variable.

[fhorse]

You could return the count from the query with something like:

select count (column_name) as nbr_in_group from mytable group by another_column_name

[Newsgroup_User]

and with 100 groups have 100 more database requests?...
--

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com

[cf_dev2]

I think the simplest approach is to use a counter

<CFSET groupcount=groupcount +1>.

[Guest]

The problem is -I need to compare the current group count with the total group count. I need to know the total number of groups before I start counting.

[cf_dev2]

Assuming the total count for all groups isn't the same as the query record count, you could run a QoQ to get the total count before the cfoutput loop. Then use the total count inside your cfoutput.

[didierd1999397]

Before you can count the number of groups by

<cfset groupCount = ListLen(ListRemoveDuplicates(valueList(query.name)))>

[BKBK]

To generate the row-count, you could use a query-of-query, which is effectively CFML code. Irrespective of how you output the row-data.

Here is a quick example, using ColdFusion's in-built cfdocsexample datasource:

<cfquery name = "GetCourses" dataSource = "cfdocexamples">
SELECT*
FROM courseList
</cfquery>
<cfdump var="#GetCourses#" label="GetCourses">
<p>
	<cfquery name = "GetCourseIDs" dbtype="query">
	SELECT distinct(Dept_ID)
	FROM GetCourses
	</cfquery>
	<cfoutput>Number of departments (DEPT_ID): #GetCourseIDs.recordcount#</cfoutput>
</p>

<p>
	<cfquery name = "GetCourseLevels" dbtype="query">
	SELECT distinct(CorLevel)
	FROM GetCourses
	</cfquery>
	<cfoutput>Number of Course Levels (CORLEVEL): #GetCourseLevels.recordcount#</cfoutput>
</p>