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

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

CodePudding user response：

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

CodePudding user response：

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
;