Hmm, after seeing this it looks as if the way I have it is a lot simpler and easier to handle. Currently I just put them in a PHP array and sum the array. Thanks for the input.
----- Original Message ----- From: "Tore Bostrup" <[EMAIL PROTECTED]> To: "MySQL List" <[EMAIL PROTECTED]> Sent: Sunday, March 02, 2003 1:12 PM Subject: Re: Using SUM in a select statement. > What you are looking for appears to be report layout. Keep in mind that SQL > will only return multiple rows, all with the same columns populated. > > To even get close to what you want, you can create a query that returns: > > TeamOwner, Player, Position, Goals, Points > > And then your application can massage it into the output format you want. > > A query to return BOTH detail information on players AND summary information > on owners would have to be a union of two queries - one returning the player > information and the other returning owner summary information (UNION > requires version 4.0): > > SELECT O.OwnerName, > 1 as SortOrder, > P.Player, > P.Position, > Goals, > Points > FROM owners as O > INNER JOIN teamplayers as T > ON O.OwnerID = T.OwnerID > INNER JOIN players as P > ON T.PlayerID = P.PlayerID > > UNION > > SELECT O.OwnerName, > 2 as SortOrder, > 'Total' as Player, > P.Position, > Sum(Goals) as Goals, > Sum(Points) as Points > FROM owners as O > INNER JOIN teamplayers as T > ON O.OwnerID = T.OwnerID > INNER JOIN players as P > ON T.PlayerID = P.PlayerID > GROUP BY O.OwnerName, > SortOrder, > Player, > P.Position > > ORDER BY O.OwnerName, > SortOrder, > Player, > P.Position > > > Based on this, it is basically a question of suppressing repeated values of > OwnerName. > > Adjust the query to match your actual situation. If you are using version > 3.23, AFAIK you'll either have to use two separate queries or calculate the > totals in your app from the detail data. > > HTH, > Tore. > > > ----- Original Message ----- > From: "C. Reeve" <[EMAIL PROTECTED]> > To: "MySQL List" <[EMAIL PROTECTED]> > Sent: Sunday, March 02, 2003 11:54 AM > Subject: Using SUM in a select statement. > > > > Hi, Not sure how to explain this - but here is what I need. I have a > > hockey pool database with three tables - one has the players and their > > stats, one has the team owner, and the 3rd is the reference file that ties > > the two together. > > > > I want to be able to do a query for all the players on a particular team > > that are forwards and get a grand total of all their points. I already > have > > a query that does something similar, but it just displays the individual > > players stats. > > > > Example: > > > > Team 1 Player Position Goals Points > > > > *Bob Bonk F 22 50 > > Hossa F 33 41 > > > > Total 55 91 This is > what > > I want to achieve - and then the same for the rest of the teams. > > > > *This is my query that get the individual players points. > > > > $query = "select team.name, player, position, gp, goals, ppg, gwg, shg, > ass, > > pm, shots, pim, points from roster join reference join team where > > team.idn=reference.idn and reference.idp=roster.idp and team.idn = '$team' > > and position like '$position' order by points desc"; > > > > TIA > > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
