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
