• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

recordcount a group

New Here ,
Aug 23, 2007 Aug 23, 2007

Copy link to clipboard

Copied

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.



Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 23, 2007 Aug 23, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Aug 23, 2007 Aug 23, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 23, 2007 Aug 23, 2007

Copy link to clipboard

Copied

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

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Aug 23, 2007 Aug 23, 2007

Copy link to clipboard

Copied

I think the simplest approach is to use a counter

<CFSET groupcount=groupcount +1>.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 24, 2007 Aug 24, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Aug 24, 2007 Aug 24, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 18, 2024 Mar 18, 2024

Copy link to clipboard

Copied

Before you can count the number of groups by

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 20, 2024 Mar 20, 2024

Copy link to clipboard

Copied

LATEST

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>

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation