sql - Select sport results ordering by medals -


i have table:

sport country place ski  swe 1 ski  nor 2 ski  rus 3 luge swe 1 luge usa 2 luge ger 3 bob  nor 1 bob  rus 2 bob  ger 3 

where place 1 gold, 2 silver, 3 bronze

now normal displying scenario list of countries, first max gold, silver bronze. exampe be:

swe g:2 s:0 b:0 sum:2 rus g:0 s:1 b:1 sum:2 usa g:0 s:1 b:0 sum:1 nor g:0 s:0 b:2 sum:2 

what sql query list of countries ordering way?

regards

select    country,    sum(case when place = 1 1 else 0 end) gold,    sum(case when place = 2 1 else 0 end) silver,    sum(case when place = 3 1 else 0 end) bronce,    count(*) allmedals tab group country 

for ordering result might do

order sum(4 - place) desc -- weighted medals 

Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -