I don't want to write the same query over and over again because the query is redundant.

SELECT1AS a,
       2-a ASb -- Unknown columns!
FROM hoge;--- dummy table

SELECT t.*, my_goals-enemy_goals AS goal_diff
FROM(
SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country, 
    c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
    (SELECT COUNT (g1.id) FROM goals g1 WHERE p1.id=g1.pairing_id) AS my_goals,
    (
        SELECT COUNT (g2.id) 
        FROM goals g2 
        LEFT JOIN pairing p2 ON p2.id=g2.pairing_id
        WHERE p2.my_country_id =p1.enemy_country_id AND p2.enemy_country_id =p1.my_country_id
    AS enemy_goals
FROM pairings p1
LEFT JOIN COUNTRIES c1 ON c1.id = p1.my_country_id
LEFT JOIN COUNTRIES c2 ON c2.id = p1.enemy_country_id
WHERE c1.group_name = 'C'AND c2.group_name = 'C'
ORDER BY p1.kickoff, c1.ranking
ASt;

with goal_count as(
    select p.id id, count(g.id)value
    from pairing p, goalsg
    where p.id=g.pairing_id
    group by p.id)
SELECT p1.kickoff, c1.name AS my_country, c2.name AS enemy_country,
    c1.ranking AS my_ranking, c2.ranking AS enemy_ranking,
    ifnull(g1.value,0)AS my_goals,
    ifnull(g2.value,0)AS enemy_goals,
    ifnull(g1.value,0) - ifnull(g2.value,0)AS goal_diff
FROM pairings p1
LEFT JOIN COUNTRIES c1 ON c1.id = p1.my_country_id
LEFT JOIN goal_count g1 ON g1.id = p1.id,
     pairings p2
LEFT JOIN COUNTRIES c2 ON c2.id = p2.my_country_id
LEFT JOIN goal_count g2 ON g2.id = p2.id
WHERE p1.my_country_id = p2.enemy_country_id
AND p1.enemy_country_id=p2.my_country_id
AND c1.group_name = 'C'AND c2.group_name = 'C'
ORDER BY p1.kickoff, c1.ranking;