5 Replies Latest reply on May 6, 2007 8:33 PM by Dan Bracuk

    group function in coldfusion

    silviasalsa Level 1
      Hello does anyone know how to use the group function in coldfusion
      my tables that out-puts to a html
      is outputing multiple CEENO

      for example CEENO
      11111
      11111
      11111
      11111

      someone here said to use the group function in coldfusion
      do you know where i can put it?




      <table border="2" cellpadding="2" cellspacing="0">
      <tr>
      <th>Record Number</th>
      <TH>PEA_id</TH>
      <th width="120">CEENO</th>
      <TH>Address</TH>

      <TH>ZipCode</TH>

      <th>AdvisoryDate</th>






      </tr>

      <cfloop query="getthecases">


      <tr bgcolor="<cfif currentrow mod 2>GHOSTWHITE<cfelse>WHITE</cfif>">
      <td>#CurrentRow#</td>
      <td>#PEA_id</td>
      <Td>#CEENO#</td>
      <td>#ST_NO# #ST_initial# #st_dir#</td>
      <td>#ZipCode#</td>





      <td>#dateformat(ADVISORY_DATE,"mm/dd/yyyy")#</td>









      </tr>
      </cfloop>

      </table>
        • 1. Re: group function in coldfusion
          insuractive Level 3
          The Group functionality built into the <Cfoutput> tag works something like this:

          <!--- CFOUTPUT LOOP 1 (UNIQUE VALUES OF colA) --->
          <cfoutput query="myQuery" group="colA">
          <p>#colA#</p>

          <!--- CFOUTPUT LOOP 2 (all records associated with unique ColA value)
          <cfoutput>
          #colB#
          </cfoutput>

          </cfoutput>

          The outer cfoutput block (the one with the group="" attribute) will loop once for every unique value of the column you specify in the group attribute (assuming the values are ordered by ColA). The inner <cfoutput> block will loop for each row in the table that match that unique value of ColA.

          TABLE:
          ColA, ColB, ColC
          A, 1, 0
          A, 2, 0
          B, 0, 0
          C, 3, 1

          Using the code above, you would see an output like this:

          <p>A</p>
          1
          2
          <p>B</p>
          0
          <p>C</p>
          3

          Hope that helps illuminate the problem. The official CF documentation can probably give you a little more indepth explaination.
          • 2. Re: group function in coldfusion
            silviasalsa Level 1
            is this similar to group by in sql?

            eliminating redunacies right?


            where in my code should this be group be in. ?

            Thanks
            • 3. Re: group function in coldfusion
              Dan Bracuk Level 5
              quote:

              Originally posted by: silviasalsa
              is this similar to group by in sql?

              eliminating redunacies right?

              Thanks

              wrong

              The group by clause in sql is used in conjunction with aggregates, like count, sum, etc. The group attribute in cfquery enables you fine tune the way you display your data.
              • 4. Re: group function in coldfusion
                silviasalsa Level 1
                thanks
                how and where do i place the group function on my code below?

                <table border="2" cellpadding="2" cellspacing="0">
                <tr>
                <th>Record Number</th>
                <TH>PEA_id</TH>
                <th width="120">CEENO</th>
                <TH>Address</TH>

                <TH>ZipCode</TH>

                <th>AdvisoryDate</th>






                </tr>

                <cfloop query="getthecases">


                <tr bgcolor="<cfif currentrow mod 2>GHOSTWHITE<cfelse>WHITE</cfif>">
                <td>#CurrentRow#</td>
                <td>#PEA_id</td>
                <Td>#CEENO#</td>
                <td>#ST_NO# #ST_initial# #st_dir#</td>
                <td>#ZipCode#</td>





                <td>#dateformat(ADVISORY_DATE,"mm/dd/yyyy")#</td>









                </tr>
                </cfloop>

                </table>
                • 5. Re: group function in coldfusion
                  Dan Bracuk Level 5
                  There is no group function. There is a group attribute in cfoutput. You are using cfloop instead of cfoutput so it wouldn't apply.

                  You have also not explained what it is you want to group and why. Those are rather significant details.