I cannot find similar question related specially to my case. So I post this question. And also ne need to provide sample data because below informations are enough.
My matches table is:
| home_team_id | away_team_id | htft |
|---|---|---|
| 11 | 9 | 2/2 |
| 18 | 17 | X/2 |
| 20 | 19 | 2/2 |
| 1 | 8 | X/2 |
| 4 | 12 | 1/X |
| 14 | 2 | 2/2 |
| 3 | 16 | 1/1 |
| 13 | 15 | 2/X |
| 7 | 10 | 1/1 |
| 5 | 6 | 1/1 |
| 9 | 13 | 1/1 |
and teams table is:
| team_id | team_name |
|---|---|
| 5 | Arsenal |
| 6 | Aston Villa |
| 7 | Brentford |
| 8 | Brighton & Hove Albion |
| 9 | Burnley |
| 12 | Chelsea |
| 19 | Crystal Palace |
| 18 | Everton |
| 20 | Leeds United |
| 31 | Leicester City |
| 32 | Liverpool |
I have 2 queries. One is show home matches count and second one is away team count results. There are working fine. But how can I merge these two queries at one query?
My tables structures are;
teams table fields: team_id, team_name
matches table fields: home_team_id, away.team_id, htft
Home Query:
SELECT m.home_team_id,t.team_name as Home,
SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1/1',
SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'X/1',
SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'X/X',
SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2/2',
SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'X/2',
SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1/X',
SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2/X',
SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2/1',
SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1/2'
FROM matches m, teams t
where m.home_team_id = t.team_id
GROUP BY m.home_team_id;
Output:
| home_team_id | Home | 1/1 | X/1 | X/X | 2/2 | X/2 | 1/X | 2/X | 2/1 | 1/2 |
|---|---|---|---|---|---|---|---|---|---|---|
| 5 | Arsenal | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7 | Brentford | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 9 | Burnley | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 18 | Everton | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 20 | Leeds United | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
Away Query:
SELECT m.away_team_id,t.team_name as Away,
SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1/1',
SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'X/1',
SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'X/X',
SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2/2',
SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'X/2',
SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1/X',
SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2/X',
SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2/1',
SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1/2'
FROM matches m, teams t
where m.away_team_id = t.team_id
GROUP BY m.away_team_id;
Output:
| away_team_id | Away | 1/1 | X/1 | X/X | 2/2 | X/2 | 1/X | 2/X | 2/1 | 1/2 |
|---|---|---|---|---|---|---|---|---|---|---|
| 6 | Aston Villa | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 8 | Brighton & Hove Albion | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 9 | Burnley | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
| 12 | Chelsea | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| 19 | Crystal Palace | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 |
How can I merge or combine both queries. I think if both queries possible to merge it will give the result I expected.
Thanks in advance to those who will help.
Expected result :
| Team | HTFT(1/1) | HTFT(X/1) | HTFT(X/X) | HTFT(2/2) | HTFT(X/2) | HTFT(1/X) | HTFT(2/X) | HTFT(2/1) | HTFT(1/2) |
|---|---|---|---|---|---|---|---|---|---|
| Arsenal | 2 | 1 | 0 | 1 | 1 | 2 | 1 | 0 | 1 |
| Aston Villa | 0 | 1 | 0 | 1 | 1 | 2 | 1 | 0 | 1 |
| Everton | 1 | 1 | 2 | 1 | 0 | 1 | 1 | 2 | 1 |
Current Home results Query
Current Away results Query
