Return list of Students by ZipCode Count-CodePudding

I am trying to get a list of students that live in the same zip code where zip code count > 1.

I tried the following and get nothing in my query. If I remove s.Student, I get results of zipcode and count, but I want to include student also.

SELECT s.Student, z.ZipCode, COUNT(s.ZipCodeId) As 'Zip Code Count'
FROM Students s
INNER JOIN ZipCodes z ON z.ZipCodeId = s.ZipCodeId
GROUP BY s.Student, z.ZipCode
HAVING COUNT(z.ZipCode) > 1

Below are the database tables I am using.

CREATE TABLE [dbo].[Instructors](
    [InstructorId] [int] IDENTITY(1,1) NOT NULL,
    [Instructor] [varchar](50) NOT NULL,
    [ZipCodeId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Students](
    [StudentId] [int] IDENTITY(1,1) NOT NULL,
    [Student] [varchar](50) NOT NULL,
    [ZipCodeId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ZipCodes](
    [ZipCodeId] [int] IDENTITY(1,1) NOT NULL,
    [ZipCode] [varchar](9) NULL,
    [City] [varchar](50) NULL,
    [State] [varchar](25) NULL
) ON [PRIMARY]

CodePudding user response：

I think you need to query the Zip Codes which are used more than once, then join the Students on along with the Zip Code details e.g.

SELECT S.Student, Z.ZipCode, Z1.Num AS "Zip Code Count"
FROM (
    SELECT COUNT(*) Num, ZipCodeId
    FROM Students S
    GROUP BY ZipCodeId
    HAVING COUNT(*) > 1
) Z1
INNER JOIN Students S on S.ZipCodeId = Z1.ZipCodeId
INNER JOIN ZipCodes Z on Z.ZipCodeId = Z1.ZipCodeId;

Note: You don't use single quotes (') to delimit a column name - you use double quotes (") or square brackets ([]).

Also, sample data would allow testing of our solutions.