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