Back

Answered by battlethechamp

SELECT 	CASE WHEN team2 = 'Barcelona' THEN 'Away'
			 ELSE 'Home' END AS home_away,
		CASE WHEN (team1 = 'Barcelona') AND (score1>score2) THEN 'Win'
        	 WHEN (team2 = 'Barcelona') AND (score2>score1) THEN 'Win'
             WHEN (score1=score2) THEN 'Tie'
             WHEN (team1 = 'Barcelona') AND (score1<score2) THEN 'Loss'
             WHEN (team2 = 'Barcelona') AND (score2<score1) THEN 'Loss'
             END AS result,
       COUNT(*) AS num 
       FROM spi_soccer 
       WHERE (team1 in('Barcelona') OR team2 in('Barcelona'))
       GROUP BY home_away, result
       HAVING result IS NOT NULL
       ORDER BY home_away, result;

Answered by Waytan

SELECT 	CASE WHEN team2 = 'Barcelona' THEN 'Away'
			 ELSE 'Home' END AS home_away,
		CASE WHEN (team1 = 'Barcelona') AND (score1>score2) THEN 'Win'
        	 WHEN (team2 = 'Barcelona') AND (score2>score1) THEN 'Win'
             WHEN (score1=score2) THEN 'Tie'
             WHEN (team1 = 'Barcelona') AND (score1<score2) THEN 'Loss'
             WHEN (team2 = 'Barcelona') AND (score2<score1) THEN 'Loss'
             END AS result,
       COUNT(*) AS num 
       FROM spi_soccer 
       WHERE (team1 in('Barcelona') OR team2 in('Barcelona'))
       GROUP BY home_away, result
       HAVING result IS NOT NULL
       ORDER BY home_away, result;

Answered by townimbecile

SELECT 	CASE WHEN team2 = 'Barcelona' THEN 'Away'
			 ELSE 'Home' END AS home_away,
		CASE WHEN (team1 = 'Barcelona') AND (score1>score2) THEN 'Win'
        	 WHEN (team2 = 'Barcelona') AND (score2>score1) THEN 'Win'
             WHEN (score1=score2) THEN 'Tie'
             WHEN (team1 = 'Barcelona') AND (score1<score2) THEN 'Loss'
             WHEN (team2 = 'Barcelona') AND (score2<score1) THEN 'Loss'
             END AS result,
       COUNT(*) AS num 
       FROM spi_soccer 
       WHERE (team1 in('Barcelona') OR team2 in('Barcelona'))
       GROUP BY home_away, result
       HAVING result IS NOT NULL
       ORDER BY home_away, result;

Answered by wlldodsn

SELECT 	CASE WHEN team2 = 'Barcelona' THEN 'Away'
			 ELSE 'Home' END AS home_away,
		CASE WHEN (team1 = 'Barcelona') AND (score1>score2) THEN 'Win'
        	 WHEN (team2 = 'Barcelona') AND (score2>score1) THEN 'Win'
             WHEN (score1=score2) THEN 'Tie'
             WHEN (team1 = 'Barcelona') AND (score1<score2) THEN 'Loss'
             WHEN (team2 = 'Barcelona') AND (score2<score1) THEN 'Loss'
             END AS result,
       COUNT(*) AS num 
       FROM spi_soccer 
       WHERE (team1 in('Barcelona') OR team2 in('Barcelona'))
       GROUP BY home_away, result
       HAVING result IS NOT NULL
       ORDER BY home_away, result;

Answered by sbw0125

select home_away, result, count(result) as num from (
select id, date, team1, team2, score1, score2,
case
	when team1 = 'Barcelona' then 'Home'
    when team2 = 'Barcelona' then 'Away'
end as home_away,
case
	when score1 = score2 then 'Tie'
	when team1 = 'Barcelona' and score1 > score2 then 'Win'
	when team1 = 'Barcelona' and score1 < score2 then 'Loss'
	when team2 = 'Barcelona' and score2 > score1 then 'Win'
	when team2 = 'Barcelona' and score2 < score1 then 'Loss'
end as result
from spi_soccer where team1 in ('Barcelona') or team2 in ('Barcelona')
) A where result is not null group by A.home_away, A.result order by home_away, result;

Answered by anonymousUser

select home_away, result, count(result) as num from (
select id, date, team1, team2, score1, score2,
case
	when team1 = 'Barcelona' then 'Home'
    when team2 = 'Barcelona' then 'Away'
end as home_away,
case
	when score1 = score2 then 'Tie'
	when team1 = 'Barcelona' and score1 > score2 then 'Win'
	when team1 = 'Barcelona' and score1 < score2 then 'Loss'
	when team2 = 'Barcelona' and score2 > score1 then 'Win'
	when team2 = 'Barcelona' and score2 < score1 then 'Loss'
end as result
from spi_soccer where team1 in ('Barcelona') or team2 in ('Barcelona')
) A where result is not null group by A.home_away, A.result order by home_away, result;

Answered by jrz1977

select home_away, result, count(result) as num from (
select id, date, team1, team2, score1, score2,
case
	when team1 = 'Barcelona' then 'Home'
    when team2 = 'Barcelona' then 'Away'
end as home_away,
case
	when score1 = score2 then 'Tie'
	when team1 = 'Barcelona' and score1 > score2 then 'Win'
	when team1 = 'Barcelona' and score1 < score2 then 'Loss'
	when team2 = 'Barcelona' and score2 > score1 then 'Win'
	when team2 = 'Barcelona' and score2 < score1 then 'Loss'
end as result
from spi_soccer where team1 in ('Barcelona') or team2 in ('Barcelona')
) A where result is not null group by A.home_away, A.result order by home_away, result;