7 Replies Latest reply on Aug 16, 2006 6:16 AM by SteveBryant

    Grouping problem

    richy2424
      Hi i have the query below which works but i need to group my output by PlayerID i have tried adding this to my query output but this groups my PlayersName but not the TotalPoints which is from a different table

      can i group the 2 PlayerID from each table?

      ie <cfoutput query="GetPlayers" Group="HH.PlayerID, FF.PlayerID">
        • 1. Re: Grouping problem
          The ScareCrow Level 1
          If I understand this correctly, you should be able to do<cfquery name="GetPlayers" datasource="crick">
          SELECT FF.PlayerID,
          HH.PlayerName, BonusMom, BonusTrs, BonusTwt,
          SUM(FF.Points) AS TotalPoints
          FROM PlayersTable HH, PointsTable FF
          WHERE HH.PlayerID > 1 AND HH.PlayerID = FF.PlayerID
          GROUP BY FF.PlayerID, HH.PlayerName, BonusMom, BonusTrs, BonusTwt
          ORDER BY TotalPoints DESC
          </cfquery>


          Ken
          • 2. Re: Grouping problem
            richy2424 Level 1
            Hi yes i tried that but still the same this is my output

            <cfoutput query="GetPlayers">
            <tr>
            <td width="150" height="21" valign="bottom" class="MainBodyGreenSmall"><div align="left" class="style2">#GetPlayers.PlayerName#</div></td>
            <td valign="bottom" class="MainBodyGreenSmall"><div align="center" class="style2">
            <div align="center">#GetPlayers.BonusMom#</div></div></td>
            <td valign="bottom" class="MainBodyGreenSmall"><div align="center" class="style2">
            <div align="center">#GetPlayers.BonusTwt#</div></div></td>
            <td valign="bottom" class="MainBodyGreenSmall"><div align="center"><span class="style2">#GetPlayers.BonusTrs#</span></div></td>
            <td valign="bottom" class="MainBodyGreenSmall"><div align="center"><span class="style2">#GetPlayers.TotalPoints#</span></div></td>
            <td> </td>
            </tr>
            </cfoutput>
            • 3. Re: Grouping problem
              The ScareCrow Level 1
              Yes, but then you need to use the group attribute in the cfoutput tag

              <cfoutput query="GetPlayers" group="PlayerID">
              <tr>
              <td width="150" height="21" valign="bottom" class="MainBodyGreenSmall"><div align="left" class="style2">#GetPlayers.PlayerName#</div></td>
              <td valign="bottom" class="MainBodyGreenSmall"><div align="center" class="style2">
              <div align="center">#GetPlayers.BonusMom#</div></div></td>
              <td valign="bottom" class="MainBodyGreenSmall"><div align="center" class="style2">
              <div align="center">#GetPlayers.BonusTwt#</div></div></td>
              <td valign="bottom" class="MainBodyGreenSmall"><div align="center"><span class="style2">#GetPlayers.BonusTrs#</span></div></td>
              <td valign="bottom" class="MainBodyGreenSmall"><div align="center"><span class="style2">#GetPlayers.TotalPoints#</span></div></td>
              <td> </td>
              </tr>
              </cfoutput>

              This should then output what you want.

              Ken
              • 4. Re: Grouping problem
                richy2424 Level 1
                ok yes that groups the playerID but not the points from the other table

                ie in my PlayersTable i have

                John (PlayerID = 1)
                Paul (PlayerID = 2)
                Chris (PlayerID = 3)

                then in my PointsTable i have

                50 (PlayerID = 3)
                50 (PlayerID = 3)
                50 (PlayerID = 1)

                so my output should show

                Chris = 100
                John = 50
                Paul = 0

                but what i am getting is
                Chris = 50
                John = 50
                Paul = 0

                it is not grouping from the PointsTable aswell

                • 5. Re: Grouping problem
                  SteveBryant
                  Grouping doesn't do summation, it just displays the data in the first row of a concurrent set of rows where the values for the grouped field match.

                  Best, I think to do that kind of summation and grouping in the SQL itself. Failing that, you can do the math in the output (using the inner loop of the cfoutput for the math and then displaying the result of the grouping.

                  Again, best route is in the SQL. Look into GROUP BY and SUM.
                  • 6. Re: Grouping problem
                    richy2424 Level 1
                    Hi thanks for that

                    i have got group by and sum in my sql statement but still no luck

                    any ideas
                    • 7. Re: Grouping problem
                      SteveBryant Level 1
                      Sorry, missed that.

                      Given that you are grouping by four (4) fields, the summation will only happen for records where all four fields match. Do you think that could be the problem.

                      As an experiment, comment out the ", BonusMom, BonusTrs, BonusTwt" from the SELECT and GROUP BY clauses and do a dump of the query to see if it has what you want.

                      If it does, you may be able to leave the GROUP BY with just PlayerName (although I would suggest grouping by the PlayerID instead). Then you can use an aggregate function on the other fields. For example, with most databases you can use Max() even against a text field.

                      This is often a way around needing fields in the resultset that don't need summation by aren't really part of the grouping either.