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

group subtotals esp in header

New Here ,
Aug 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied

When doing a group on a query, is there a simple way to identify the number of rows in each group/section in the HEADER of the section?

I do understand that I could run over the query and setting up an array or structure to total the nubmer of rows in each group. And then output the query and display the array. I'm looking for something a bit more straightforward. Perhaps something built in, even? If not, is this something worth asking for?

<cfoutput Query'="MyQueryName" Group="ColumnToGroupBy">
My Section Title - there will #x# rows in this section
<cfoutput>
A single row<br>
</cfoutput>
My section footer - there were #x# rows in this section
</cfoutput>
TOPICS
Advanced techniques

Views

452

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 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied

Doing a q of q to get your group totals is probably less work than looping through and building an array.

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 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied

When you say do a q of q, I'm not seeing the most efficient way of that happening. Are you saying as I loop over the output, each time I hit the group header, I run a query of the query to get the record count? Wouldn't that be slow and expensive timewise?

Are you saying:

<cfoutput Query'="MyQueryName" Group="ColumnToGroupBy">
<cfquery query="MyQueryName" Name="GroupCount">
Select Count(ID) AS X
From MyQueryName
Where ColumnToGroupBy = #ColumnToGroupBy#
</cfquery>

My Section Title - there will #GroupCount.X# rows in this section
<cfoutput>
A single row<br>
</cfoutput>
My section footer - there were #x# rows in this section
</cfoutput>


I've had other people suggest using Oracle specific SQL solutions, which sound fast, but not so good if you aren't using Oracle.

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 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied

LATEST
quote:

Originally posted by: labst
When you say do a q of q, I'm not seeing the most efficient way of that happening. Are you saying as I loop over the output, each time I hit the group header, I run a query of the query to get the record count? Wouldn't that be slow and expensive timewise?

It would be slow, but that is not what I am saying. I am saying that instead of "I do understand that I could run over the query and setting up an array or structure to total the nubmer of rows in each group"
do this in a q of q

select thegroupingfield, count(thegroupingfield) thecount
from yourquery
group by thegroupingfield

Then, as you are outputting your data, pick up the thecount field from this query as your rows per section.

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
Guest
Aug 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied


It might be simpler if your resultset returned another column which indicated
the number of rows in _this_ section.

Meaning, you're not going to return any additional rows but rather just simply
return a new column and for each row in the group for the given column would
have the same value; which would be the number of rows in their group.

I'm not sure of the syntax but in your SELECT list you would use the COUNT(...)
function. You would probably also need a GROUP BY clause along with
the WITH ROLLUP modifier. Finally, you'll probably need to filter out the rollup
rows so that the only thing you are left with is the original resultset you were
working with but with a new column.

On second thought... if you're the one doing the CF and DB, my method may
not be the path of least resistance!

Good luck!

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
Guest
Aug 18, 2006 Aug 18, 2006

Copy link to clipboard

Copied


I found a simpler solution on-line...

SELECT iTable.total, s.continent, s.country
FROM sales s
INNER JOIN (
SELECT continent, COUNT(continent) AS total
FROM sales GROUP BY continent
) iTable
ON s.continent = iTable.continent

So now you can be in any row of the resultset and you will know (by looking
at the total column) how many rows are in that group.

I tried this out and it works!

I know it's a bit of hack so when you find a more approriate solution please
let us know.

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