howto sum columns in one table with different conditions

[jordennabbe]

Hello,

 

Can someone help please.

I have a table with a

 

player_home,

player_away,

team_home,

team_away,

score_home,

score_away

 

player_home always has team_home and score_home.

player_away always has team_away and score_away

 

Problem is on one row the player 'Rob' can be player_home and the next row he is player_away.

 

What I wanna get out of this table is..

How many times did 'Rob' win a game?

How can I query this?

I appreciate the help!

:-)

[Barand]

Use a combination of subquery and union. (Assumes the tablename is `fixture`)

SELECT player
	, SUM(win) as won
	, SUM(draw) as drawn
	, SUM(lose) as lost
FROM (
	SELECT 
		player_home as player
		, score_home > score_away as win
		, score_home = score_away as draw
		, score_home < score_away as lose
		FROM fixture
	UNION ALL 
        SELECT
		player_away as player
		, score_away > score_home as win
		, score_away = score_home as draw
		, score_away < score_home as lose
		FROM fixture
	) results
GROUP BY player;

Edited January 21, 2017 by Barand