recordcount a group

Forum|Forum|18 years ago
August 23, 2007
7 replies
1783 views

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.

This topic has been closed for replies.

BKBK

Community Expert

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>

D

didierd1999397

Inspiring

Before you can count the number of groups by

C

cf_dev2

Inspiring

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.

C

cf_dev2

Inspiring

I think the simplest approach is to use a counter

<CFSET groupcount=groupcount +1>.

A

Anonymous

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.

N

Newsgroup_User

Inspiring

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

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

F

fhorse

Participant

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

D

Dan_Bracuk

Inspiring

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