How to merge two SQL/MySQL queries in to a single query?

user_18103367 0
Viewed 37

I checked several post related my subject, but I cannot find my answer. I also posted similar question but can't get any answer so I prepared posting again.

Because my table's columns have same values and want to count by team and HTFT values.

Expected result :

Team Totals 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)
Adana Demirspor 1 0 1 0 1 1 0 0 0
Alanyaspor 2 0 0 2 0 0 0 0 0
Altay 2 0 0 1 1 0 0 0 0
Antalyaspor 1 2 0 0 0 1 0 0 0
Beşiktaş 3 0 1 0 0 0 0 0 0
Çaykur Rizespor 2 0 1 0 1 0 0 0 0
Fatih Karagümrük 3 0 1 0 0 0 0 0 0
Fenerbahçe 0 1 1 0 2 0 0 0 0
Galatasaray 0 1 0 0 0 0 2 0 0
Gaziantep 2 1 1 0 0 0 0 0 0
Giresunspor 2 0 0 1 0 0 0 0 0
Göztepe 0 0 0 0 1 2 0 1 0
Hatayspor 1 1 0 0 1 0 1 0 0
İstanbul Başakşehir 0 0 0 2 0 0 0 2 0
Kasımpaşa 2 0 0 0 0 0 2 0 0
Kayserispor 2 0 0 1 0 1 0 0 0
Konyaspor 1 0 1 0 1 0 0 1 0
Sivasspor 1 0 1 0 1 1 0 0 0
Trabzonspor 1 0 0 2 0 0 1 0 0
Yeni Malatyaspor 2 0 0 1 1 0 0 0 0

I have below fiddle link that have my tables, sample database and two queries.

My Sample database fiddle link

I want to merge two queries in one query is it possible?

Regards,

mysql sql
2 Answers
WITH
Home AS (
SELECT m.home_team_id AS team_id,t.team_name,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS 'h 1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'h X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'h X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS 'h 2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'h X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS 'h 1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS 'h 2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS 'h 2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS 'h 1/2'
    FROM matches m 
    INNER JOIN teams t on m.home_team_id = t.team_id
GROUP BY 1,2
),
Away AS (
SELECT m.away_team_id AS team_id,t.team_name,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS 'a 1/1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'a X/1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'a X/X',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS 'a 2/2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'a X/2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS 'a 1/X',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS 'a 2/X',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS 'a 2/1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS 'a 1/2'
    FROM matches m
    INNER JOIN teams t on m.away_team_id = t.team_id
GROUP BY 1,2
)
SELECT *
FROM Home
JOIN Away USING (team_id, team_name)
ORDER BY 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c7d73ac3d6f892d7c9d415cf3baef3d

user_101387340

Please try this,

SELECT
Home as Team
,SUM(1by1) as '1/1'
,SUM(Xby1) as 'X/1'
,SUM(XbyX) AS 'X/X'
,SUM(2by2) AS '2/2'
,SUM(Xby2) AS 'X/2'
,SUM(1byX) AS '1/X'
,SUM(2byX) AS '2/X'
,SUM(2by1) AS '2/1'
,SUM(1by2) AS '1/2'
FROM
(
SELECT m.home_team_id ,t.team_name as Home,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1by1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'Xby1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'XbyX',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2by2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'Xby2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1byX',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2byX',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2by1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1by2'
    FROM matches m 
    INNER JOIN teams t on m.home_team_id = t.team_id
GROUP BY m.home_team_id,Home

union all

SELECT m.away_team_id,t.team_name as Away,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1by1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'Xby1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'XbyX',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2by2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'Xby2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1byX',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2byX',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2by1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1by2'
    FROM matches m
    INNER JOIN teams t on m.away_team_id = t.team_id
GROUP BY m.away_team_id,Away
)A
GROUP BY TEAM
order by 1
;

query with team id filter,

SELECT
home_team_id as team_id
,Home as Team
,SUM(1by1) as '1/1'
,SUM(Xby1) as 'X/1'
,SUM(XbyX) AS 'X/X'
,SUM(2by2) AS '2/2'
,SUM(Xby2) AS 'X/2'
,SUM(1byX) AS '1/X'
,SUM(2byX) AS '2/X'
,SUM(2by1) AS '2/1'
,SUM(1by2) AS '1/2'
FROM
(
SELECT m.home_team_id ,t.team_name as Home,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1by1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'Xby1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'XbyX',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2by2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'Xby2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1byX',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2byX',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2by1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1by2'
    FROM matches m 
    INNER JOIN teams t on m.home_team_id = t.team_id
GROUP BY m.home_team_id,Home

union all

SELECT m.away_team_id,t.team_name as Away,
         SUM(CASE WHEN m.htft = '1/1' THEN 1 ELSE 0 END) AS '1by1',
         SUM(CASE WHEN m.htft = 'X/1' THEN 1 ELSE 0 END) AS 'Xby1',
         SUM(CASE WHEN m.htft = 'X/X' THEN 1 ELSE 0 END) AS 'XbyX',
         SUM(CASE WHEN m.htft = '2/2' THEN 1 ELSE 0 END) AS '2by2',
         SUM(CASE WHEN m.htft = 'X/2' THEN 1 ELSE 0 END) AS 'Xby2',
         SUM(CASE WHEN m.htft = '1/X' THEN 1 ELSE 0 END) AS '1byX',      
         SUM(CASE WHEN m.htft = '2/X' THEN 1 ELSE 0 END) AS '2byX',
         SUM(CASE WHEN m.htft = '2/1' THEN 1 ELSE 0 END) AS '2by1',
         SUM(CASE WHEN m.htft = '1/2' THEN 1 ELSE 0 END) AS '1by2'
    FROM matches m
    INNER JOIN teams t on m.away_team_id = t.team_id
GROUP BY m.away_team_id,Away
)A
where home_team_id=8
GROUP BY TEAM, home_team_id
order by 1
;
user_119137650
Related