8 Replies Latest reply on Oct 7, 2008 10:42 AM by BKBK

    HOw Do I count and identify the number of groups in a groups output.

    Chris Luksha
      I have a query that reads like this..

      SELECT s.spec_Sort,s.spec_ID, s.spec_Name, b.bus_Name, b.bus_ID
      FROM (tbl_businesses as b INNER JOIN tbl_SpecBusRel as sb ON b.bus_ID = sb.specbus_busid) INNER JOIN tbl_specialties as s ON sb.specbus_specid = s.spec_ID
      ORDER BY s.spec_Sort, b.bus_Name

      and then I am outputing like this...
      <cfoutput query="rsGetSpecialties" group="spec_Name">
      <cfif THIS IS WHAT I NEED)></td><td></cfif>
      <h2><a class="specialty" href="javascript;" onclick="dwfaq_ToggleOMaticDisplay(this,'#spec_ID#');return document.MM_returnValue">#spec_Name#</a></h2>
      <div id="#spec_ID#" style="display:none">
      <cfoutput><p><a href="#request.details#?bus_ID=#bus_ID#">#bus_Name#</a></p></cfoutput>
      </div>
      </cfoutput>

      This displays in this manner...
      Specialty
      dealer
      Specialty
      dealer1
      dealer2
      Etc...

      What I am trying to do is find out how many specialties there are, figure out where the middle is so I can insert a new table data cell and start a new column.

      Basically - two columns with the data divided in half by specialty NOT by the number of dealers.

      I hope that makes sense. Of course I am trying to do it w/ as little query action as possible. My only solution may be to have more than one query and the second only finds a list of the sepcialties that contain the dealers and then find the middle one and simply say if current specialty = middleSpecialty then start a new column.

      Thanks all,
      Chris
        • 1. Re: HOw Do I count and identify the number of groups in a groups output.
          BKBK Adobe Community Professional & MVP
          1) Remove the nested cfoutput tag. It is redundant.

          2) The column you use as cfouput's group attribute must also be in the query's order-by clause.

          • 2. Re: HOw Do I count and identify the number of groups in a groups output.
            BKBK Adobe Community Professional & MVP
            To get the number of occurrences of each specialty, do a query of a query

            <cfquery dbtype = "query" name = "getNoOfSpecialties">
            SELECT spec_Name, count(spec_Name) as noOfOccurrences
            FROM rsGetSpecialties
            GROUP BY spec_Name
            </cfquery>
            <cfdump var="#getNoOfSpecialties#">

            • 3. Re: HOw Do I count and identify the number of groups in a groups output.
              Chris Luksha Level 1
              Thanks for the replies. I don't need the number of occurances of each specialties - I simply need to know how many specialties there are and then split them in half.

              This may be the long way around -but it is what I did...
              <cfquery name="rsGetSpecialties" >
              SELECT s.spec_Sort,s.spec_ID, s.spec_Name, b.bus_Name, b.bus_ID, b.bus_Pcity, b.bus_PState
              FROM (tbl_businesses as b INNER JOIN tbl_SpecBusRel as sb ON b.bus_ID = sb.specbus_busid) INNER JOIN tbl_specialties as s ON sb.specbus_specid = s.spec_ID
              ORDER BY s.spec_Sort, b.bus_Name
              </cfquery>
              <cfset specs = valuelist(rsGetSpecialties.spec_ID)>
              <cfquery name="rsListSpecialties">
              SELECT spec_ID,spec_Name
              FROM tbl_specialties
              WHERE spec_ID in(#specs#)
              ORDER BY spec_Name
              </cfquery>
              <cfset middle = int(rsListSpecialties.recordcount/2)>
              <cfset specs = valuelist(rsListSpecialties.spec_Name)>
              <cfset middle = listgetat(#specs#,middle)>


              then displayed like this...
              <td valign="top" width="50%">
              <cfoutput query="rsGetSpecialties" group="spec_Name">
              <h2><a class="specialty" href="javascript;" onclick="dwfaq_ToggleOMaticDisplay(this,'#spec_ID#');return document.MM_returnValue">#spec_Name#</a></h2>
              <div id="#spec_ID#" style="display:none">
              <cfoutput><a href="#request.details#?bus_ID=#bus_ID#">#bus_Name# (#bus_PCity#, #bus_PState#)</a><br /></cfoutput>
              </div>
              *****This is the line that does the splitting *****
              <cfif spec_Name EQ middle></td><td valign="top" width="50%"></cfif>
              </cfoutput>
              </td>


              As for removing the extraneous cfoutput --> I thought that I needed that in order to show the material within the grouped data. HOw does CF know what data to repeat and what data to not repeat in a grouped output? I will have to look this one up some more.

              thanks for the help
              • 4. Re: HOw Do I count and identify the number of groups in a groups output.
                BKBK Adobe Community Professional & MVP
                From what you've explained, you don't have to go to all that trouble to find the number of specialties. Since there is a spec_ID, which is apparently unique, you could just do

                <cfquery dbtype = "query" name = "getNoOfSpecialties">
                SELECT count(spec_id) as noOfSpecialties
                FROM rsGetSpecialties
                </cfquery>
                <cfdump var="#getNoOfSpecialties#">

                You ended with a question about the first cfoutput tag. I wasn't talking about that one. By "nested", I meant the cfoutput tag within the cfoutput tag.


                • 5. Re: HOw Do I count and identify the number of groups in a groups output.
                  Chris Luksha Level 1
                  Thanks for the query of the query - I forgot all aobut doing that.

                  As for the nested output - I too was referring to the nested output.

                  My understanding is that when you used the 'group=' that you needed to put in the second output wrapper in order to get the unique information output without repeating the duplicate information.

                  i.e. I thought this ..

                  <cfoutput group="spec_D">
                  #spec_Name#
                  #dealer#<Br>
                  </cfoutput>

                  would result in
                  spec1 dealer1
                  spec1 dealer2
                  spec1 dealer3

                  What I want is

                  spec1 dealer1
                  dealer2
                  dealer3

                  and that would take this..
                  <cfoutput group="spec_D">
                  #spec_Name#
                  <cfoutput>#dealer#<Br></cfoutput>
                  </cfoutput>
                  • 6. Re: HOw Do I count and identify the number of groups in a groups output.
                    BKBK Adobe Community Professional & MVP
                    Do this and see what happens

                    cfoutput group="spec_D">
                    #spec_Name#
                    #dealer#<Br>
                    </cfoutput>


                    • 7. Re: HOw Do I count and identify the number of groups in a groups output.
                      Chris Luksha Level 1
                      unfortunately all it does is returne

                      specname
                      dealer
                      specname
                      dealer
                      specname
                      dealer

                      sadly none of the specials that contain multiple dealers - returns multiple dealers :(

                      It was fun trying something new
                      • 8. Re: HOw Do I count and identify the number of groups in a groups output.
                        BKBK Adobe Community Professional & MVP
                        OK. Do you now have what you want?