Grouping problem

[Guest]

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">

[The_ScareCrow]

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

[Guest]

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>

[The_ScareCrow]

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

[Guest]

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

[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.

[Guest]

Hi thanks for that

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

any ideas

[SteveBryant · Accepted solution]

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.