Which year saw the highest and lowest no of countries participating in olympics?

Question

I'm new to SQL & practicing OLYMPICS dataset on kaggle & unable to query out the result in single row. The question mark is where I am stuck

select games, count(DISTINCT r.region) AS a, count(DISTINCT r.region) AS b
from oly
JOIN regions r 
  ON oly.noc = r.noc
group by games
order by a ASC
LIMIT 1

?????

select games, count(DISTINCT r.region) AS B
from oly
JOIN regions r 
  ON oly.noc = r.noc
GROUP BY games
ORDER BY  B desc
LIMIT 1 ;

[1]: https://www.kaggle.com/heesoo37/120-years-of-olympic-history-athletes-and-results (DATASET) [2]: https://i.stack.imgur.com/6llUh.jpg (SAMPLE DATA) — AmitS, Feb 26 '22 at 18:58
A question here should be self-contained. I.e. include a [mcve]. — jarlh, Feb 26 '22 at 19:06

score 0 · Answer 1 · answered Feb 26 '22 at 18:56

As you only have 1 Row per subquery you can use CROSS JOIN

SELECT
    a1.games,a1.a,a1.b,b1.games,b1.B
FROM
(select games, count(DISTINCT r.region) AS a, count(DISTINCT r.region) AS b  from oly
JOIN regions r 
ON oly.noc = r.noc
group by games
order by a ASC LIMIT 1) a1
CROSS JOIN 
(select games, count(DISTINCT r.region) AS B  from oly
JOIN regions r 
ON oly.noc = r.noc
GROUP BY games
ORDER BY  B desc LIMIT 1 ) b1

Which year saw the highest and lowest no of countries participating in olympics?

1 Answers1