5 Replies Latest reply on Aug 18, 2006 11:27 AM by Dan Bracuk

    group subtotals esp in header

    labst Level 1
      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>
        • 1. Re: group subtotals esp in header
          Dan Bracuk Level 5
          Doing a q of q to get your group totals is probably less work than looping through and building an array.
          • 2. Re: group subtotals esp in header
            labst Level 1
            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.
            • 3. Re: group subtotals esp in header
              <newbie /> Level 1

              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!
              • 4. Re: group subtotals esp in header
                <newbie /> Level 1

                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.
                • 5. Re: group subtotals esp in header
                  Dan Bracuk Level 5
                  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.