jeudi 7 mai 2020

Compare 2 columns and count how many times the team won in ORACLE SQL

I have a such table which already joined some tables. It's about basketball games. I want to get a result which shows team name and the number of games, and how many games a team won and lose.

GAME table:
game_id - NUMBER
game_date - DATE
location - VARCHAR2
home_teamName - VARCHAR2
away_teamName - VARCHAR2
home_point - NUMBER
away_point - NUMBER

Desired result:
team_name - VARCHAR2
games - NUMBER
win - NUMBER
lose - NUMBER
like this:
name|games|win|lose
AAA |   10   | 8 |2
BBB |   9     | 4 |4
CCC |   10   | 6 |5

For now, I managed to get the number of games each team had, using code below, but I have no idea to get the others. I'm also wondering whether I can get everything without joining tables or not.

SELECT T1.NAME, COUNT(T1.NAME) "GAME"
FROM SEE_GAME G
JOIN TEAM T1 ON G.HOME = T1.NAME
RIGHT JOIN TEAM T2 ON G.AWAY = T2.NAME
WHERE "HOME POINT" IS NOT NULL
GROUP BY T1.NAME;

result from the code above:
name|GAME
AAA | 10
BBB | 9
CCC | 10
DDD | 10

TEAM table:
team_id - NUMBER
name - VARCHAR2
location - VARCHAR2

Aucun commentaire:

Enregistrer un commentaire