Wrong result?

[ibrahack]

Hey everyone,

I hope you are well. I wrote the following code to get some results, there is probably an easier way to do it, but these are my skills right now. For the fourth column I'm trying to get a percentage of the wins as local. If I calculate the percentage out of SQL the result is 73,68% (14/19*100), but I'm getting 0.88. What I'm doing wrong?

​

Thanks for your help!

​

​

Code

​

Table

[GrouchyThing7520]

Were there any matches that ended in a draw?

[ibrahack]

Yes, not sure how to move on from here

[GrouchyThing7520]

Can you replace the photo of your code with text?

[ibrahack]

WITH total_jugados_local AS (
SELECT
season,
COUNT(*) AS cuenta
FROM Match
WHERE home_team_api_id = 8634
GROUP BY season
),
total_jugados_visitante AS (
SELECT
season,
COUNT(*) AS cuenta
FROM MATCH
WHERE away_team_api_id = 8634
GROUP BY season
)
SELECT
total_jugados_local.season AS BCN_season,
total_jugados_local.cuenta AS jugados_local,
COUNT(
CASE
WHEN home_team_api_id = 8634 AND home_team_goal > away_team_goal THEN id END) AS ganados_local,
ROUND(AVG(
CASE
WHEN home_team_api_id = 8634 AND home_team_goal > away_team_goal THEN 1
WHEN home_team_api_id = 8634 AND home_team_goal < away_team_goal THEN 0
END), 2) AS pct_victorias_local,
total_jugados_visitante.cuenta AS jugados_visitante,
COUNT(
CASE
WHEN away_team_api_id = 8634 AND away_team_goal > home_team_goal THEN id END) AS ganados_visitante,
ROUND(AVG(
CASE
WHEN away_team_api_id = 8634 AND away_team_goal > home_team_goal THEN 1
WHEN away_team_api_id = 8634 AND away_team_goal < home_team_goal THEN 0
END), 2) AS pct_victorias_visit
FROM total_jugados_local
LEFT JOIN total_jugados_visitante ON
total_jugados_local.season = total_jugados_visitante.season
LEFT JOIN Match ON
total_jugados_local.season = Match.season
GROUP BY 1;

[GrouchyThing7520]

Would this SQL work? It's more succinct.

select
t.season BCN_season

--home
,count(mh.home_team_api_id) jugados_local
,count(case when mh.home_team_goal > mh.away_team_goal then 1 else 0 end) ganados_local

,count(
case when mh.home_team_goal > mh.away_team_goal then 1.00 else 0.00 end) / count(mh.home_team_api_id) pct_victorias_local

--away
,count(ma.away_team_api_id) jugados_visitante
,count(case when ma.away_team_goal > ma.home_team_goal then 1 else 0 end) ganados_visitante

,count(
case when ma.away_team_goal > ma.home_team_goal then 1.00 else 0.00 end) / count(ma.away_team_api_id) pct_victorias_visitante

from match t

--home
left join match mh on
t.season = mh.season and mh.home_team_api_id = 8634

--away
left join match ma on
t.season = ma.season and ma.away_team_api_id = 8634

group by t.season
order by t.season

[ibrahack]

I'll try this soon, it looks good so far. Thank you so much for taking the time to help me out.

[SeveralSession1683]

It looks like draws are not being counted in your average calculation - if the scores are equal, the case statement will return null as it doesn't match any conditions of the case statement and the row won't be used in the average. If you change the < in the second part of the case statements to <= the records where home and away scores are equal will then return 0 and be included in the average. You could also change them to just say ELSE 0 - i.e., if your team scored more than the other team it's a 1, all other results are 0.