Khaba wrote:
> ... in this case I have to know the ID of the
> assistman. What if I don't know it or if I
> generally want to display a sorted list?
To find the number of assists from each player to another player:
SET @playerid = 123; // Some player ID
SELECT @playerid as player, assist, SUM(num_assists) as assists
FROM (
SELECT a1 as assist, COUNT(*) as num_assists
FROM scorelist
WHERE player = @playerid
AND a1 IS NOT NULL
GROUP BY a1
UNION ALL
SELECT a2 as assist, COUNT(*)
FROM scorelist
WHERE player = @playerid
AND a2 IS NOT NULL
GROUP BY a2
)
GROUP BY @playerid, assist;
> By the way.. can it be, that I can not use an
> aggregate field in the where clause?
>
> The following query generate an error: #1054 -
> Unknown column 'nr_of_items' in 'where clause'
>
> SELECT game, sum( p1 *2 + p2 *5 + p3 *10 + p4 *20
> ) AS nr_of_items
> FROM playergame
> WHERE player =1010
> AND nr_of_items > 0
> GROUP BY game
> ORDER BY nr_of_items DESC
No, you must use the HAVING clause to filter aggregates, , because the WHERE clause filters pre-aggregation and the HAVING clause filters post-aggregation:
SELECT game, sum( p1 *2 + p2 *5 + p3 *10 + p4 *20) AS nr_of_items
FROM playergame
WHERE player =1010
GROUP BY game
HAVING sum( p1 *2 + p2 *5 + p3 *10 + p4 *20) > 0
ORDER BY nr_of_items DESC
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com