SQL Inner Join Query Select

Question

I have an assignment question which asks:

List all RegionIDs, RegionNames and number of countries as [CountriesCount] in that region desc from highest to lowest countries count.

These are the two tables which I need

CREATE TABLE Regions (
    RegionId INT IDENTITY(1,1) PRIMARY KEY,
    RegionName VARCHAR (25) DEFAULT NULL
);

CREATE TABLE Countries (
    CountryId CHAR (2) PRIMARY KEY,
    CountryName VARCHAR (40) DEFAULT NULL,
    RegionId INT NOT NULL,
    FOREIGN KEY (RegionId) REFERENCES Regions (RegionId) 
);

I ran the following code

SELECT Countries.RegionId, RegionName, COUNT(Countries.RegionId) AS CountriesCount
FROM Regions INNER JOIN Countries WITH (NOLOCK)
ON Regions.RegionId = Countries.RegionId
GROUP BY Countries.RegionId
ORDER BY CountriesCount DESC

It's showing the following

error:Column 'Countries.RegionId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can someone please help me with this?

SQL Inner Join Query Select

0 Answers