with t1 as
(select distinct oh.games,oh.noc,region as countrys from olympics_history oh inner join olympics_history_noc_regions hnr
on hnr.noc = oh.noc order by games),
t2 as
(select games,noc,count(medal) as gold_medals from olympics_history
where medal like '%Gold%'
group by noc,games
order by games),
t3 as
(select games,noc,count(medal) as Silver_medals from olympics_history
where medal like '%Silver%'
group by noc,games
order by games),
t4 as
(select games,noc,count(medal) as Bronze_medals from olympics_history
where medal like '%Bronze%'
group by noc,games
order by games),
t5 as
(select t1.games,countrys,gold_medals,Silver_medals,Bronze_medals
from t1 inner join t2 on (t1.noc = t2.noc and t1.games = t2.games)
inner join t3 on (t2.noc = t3.noc and t2.games = t3.games)
inner join t4 on (t4.noc = t3.noc and t4.games = t3.games)
order by games,countrys)
select games,max(gold_medals)as max_gold,max(silver_medals) as max_gold,max(bronze_medals) as max_bronze from t5
group by games
order by games
before last query i got out like this enter image description here My output enter image description here Actual output needed enter image description here
Im using Oracle database my questing is - take a example in max_gold column have value of 25. that 25 gold value belong to germany. so i need output like germany-25 in max_gold column. that values group by games (ex - 1896 Summer,1900 Summer,1904 Summer). in second column You have 18.
There's no sample data so I made up my own.
I guess you don't need that many CTEs; one should do (temp in my example) as it fetches all medals "at once", while the rank analytic function ranks them (so rnk = 1 represents the top countries.
Why rank and not row_number? Because of ties - what if two (or more) countries have the same number of medals? That's also why final query utilizes listagg aggregate (and not e.g. min or max).
OK, here we go.
Sample data:
SQL> with
2 olympics_history (games, medal, region) as
3 (select 1896, 'Gold' , 'Austria' from dual union all
4 select 1896, 'Gold' , 'Austria' from dual union all
5 select 1896, 'Gold' , 'Belgium' from dual union all
6 select 1896, 'Silver', 'Germany' from dual union all
7 select 1896, 'Bronze', 'Austria' from dual union all
8 select 1896, 'Bronze', 'Austria' from dual union all
9 select 1896, 'Bronze', 'Canada' from dual union all
10 --
11 select 1900, 'Gold' , 'Germany' from dual union all
12 select 1900, 'Gold' , 'UK' from dual union all
13 select 1900, 'Silver', 'France' from dual union all
14 select 1900, 'Silver', 'France' from dual union all
15 select 1900, 'Silver', 'France' from dual union all
16 select 1900, 'Silver', 'Greece' from dual
17 ),
Query begins here:
18 temp as
19 (select games, medal, region, count(*) cnt,
20 rank() over (partition by games, medal order by count(*) desc) rnk
21 from olympics_history
22 group by games, medal, region
23 )
24 select games,
25 listagg(case when medal = 'Gold' then region || ' - ' || cnt end, ', ') within group (order by region) as gold,
26 listagg(case when medal = 'Silver' then region || ' - ' || cnt end, ', ') within group (order by region) as silver,
27 listagg(case when medal = 'Bronze' then region || ' - ' || cnt end, ', ') within group (order by region) as bronze
28 from temp
29 where rnk = 1
30 group by games;
GAMES GOLD SILVER BRONZE
---------- -------------------- -------------------- --------------------
1896 Austria - 2 Germany - 1 Austria - 2
1900 Germany - 1, UK - 1 France - 3
SQL>