Jump to content
jordennabbe

howto sum columns in one table with different conditions

Recommended Posts

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!

:-)

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.