Click here to Skip to main content
14,599,518 members
Rate this:
Please Sign up or sign in to vote.
CHAMPIONS (~500 rows)

YEAR COUNTRY NAME ROLE

2018 France Didier Deschamps Manager
2018 France Hugo Lloris Goalkeeper
2018 France Paul Pogba Midfielder
2014 Germany Joachim Loew Manager
2014 Germany Mesut Ozil Midfielder
2014 Germany Miroslav Klose Forward
2002 Brazil Da Silva Midfielder
1994 Brazil Da Silva Midfielder
1998 France Didier Deschamps Midfielder

Write a query showing how many times each country has won the world cup for countries winning the world cup at least twice.

What I am confused about is, the how many times each country has won it, when for example there is France 3 times in 2018. I'm not sure if i need a count option or select statement.

I'd be grateful for extra clarification and help with this, or if my query needs any tweaking.

What I have tried:

This is what I came up with:

SELECT YEAR, COUNTRY
FROM CHAMPIONS
WHERE COUNTRY>=2;

I hope this is right.
Posted
Updated 14-Jun-20 21:51pm
Comments
MarcusCole6833 18-Jun-20 16:37pm
   
select distinct country , year
from champions
where country != 'England'
Rate this:
Please Sign up or sign in to vote.

Solution 2

If you would like to get the names of countries which won more than one time, try this:
SELECT COUNTRY, COUNT(COUNTRY) As CountOfWins
FROM CHAMPIONS
GROUP BY COUNTRY
HAVING COUNT(COUNTRY)>=2;


For further details, please see: Aggregate Functions (Transact-SQL) - SQL Server | Microsoft Docs[^]

If you would like to get the names of countries and year also, you have to:
1. create subquery (self-join):
SELECT c1.COUNTRY, c2.[YEAR]
FROM (
    SELECT COUNTRY, COUNT(COUNTRY) As CountOfWins
    FROM CHAMPIONS 
    GROUP BY COUNTRY
    HAVING COUNT(COUNTRY)>=2
) c1 INNER JOIN CHAMPIONS c2 ON c1.COUNTRY = c2.COUNTRY
ORDER BY c1.COUNTRY, c2.[YEAR];



2. use ranking function (see: Ranking Functions (Transact-SQL) - SQL Server | Microsoft Docs[^])
SELECT COUNTRY, [YEAR], ROW_NUMBER() OVER(PARTITION BY COUNTRY ORDER BY [YEAR]) AS WinsCounter
FROM CHAMPIONS;
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

As I told you yesterday: We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

We still don't do your homework for you ...
   
Comments
OriginalGriff 14-Jun-20 12:07pm
   
You could test it, and see what it produces, then compare that against what you have been asked for. Then you could try to work out what you need to do to generate the actual answer.
Just asking others to sort it out for you doesn't teach you anything.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100