Query which outputs the number of athletes that voted for someone in the same athletePosition? [closed]

Like the title says, SQL query which outputs the number of athletes that voted for someone in the same athletePosition as himself.

I have 4 tables in my database right now, they consist of:

CREATE TABLE award(
    awardId INT IDENTITY(1, 1),
    awardName VARCHAR(30) NOT NULL,
    awardSponsor VARCHAR(100),
    CONSTRAINT pk_award_awardId PRIMARY KEY (awardId)
);   

CREATE TABLE winner(
    winnerId INT IDENTITY(1, 1),
    winnerYear INT NOT NULL,
    athleteId INT NOT NULL,
    awardId INT NOT NULL,
    CONSTRAINT pk_winner_winnerId PRIMARY KEY (winnerId),
    CONSTRAINT fk_winner_athlete FOREIGN KEY (athleteId) REFERENCES athlete (athleteId),
    CONSTRAINT fk_winner_award FOREIGN KEY (awardId) REFERENCES award (awardId)
);

CREATE TABLE athlete(
    athleteId INT IDENTITY(1, 1),
    athleteFirstName VARCHAR(20) NOT NULL,
    athleteLastName VARCHAR(30) NOT NULL,
    athleteDateOfBirth DATE,
    athleteHeight SMALLINT,
    athleteWeight TINYINT,
    athletePosition CHAR(2) NOT NULL,
    athleteBattingAvg DECIMAL(4, 3) DEFAULT 0.000,
    athleteNationality VARCHAR(30),
    teamId INT NOT NULL,
    votesForId INT,
    CONSTRAINT pk_athlete_athleteId PRIMARY KEY (athleteId),
    CONSTRAINT fk_athlete_team FOREIGN KEY(teamId) REFERENCES team (teamId),
);

CREATE TABLE team(
    teamId INT IDENTITY(1, 1),
    teamName VARCHAR(50) NOT NULL,
    teamCity VARCHAR(20) NOT NULL,
    teamState CHAR(2),
    teamCountry VARCHAR(6),
    teamManager VARCHAR(50),
    teamLeague CHAR(2) NOT NULL,
    teamStadium VARCHAR(30),
    CONSTRAINT pk_team_teamId PRIMARY KEY (teamId)
);

Here is some sample data for ATHLETE Table.

    INSERT INTO athlete
VALUES
    ('Vladimir', 'Guererro Jr.', '1999-03-16', 188, 113, '3B', 0.272, 'Canada', 1),
    ('Bo', 'Bichette', '1998-03-05', 183, 83, 'SS', 0.311, 'USA', 1),
    ('Cavan', 'Biggio', '1995-04-11', 188, 90, '2B', 0.234, 'USA', 1),
    ('Travis', 'Shaw', '1990-04-16', 193, 104, '1B', 0.157, 'USA', 1),
    ('Danny', 'Jansen', '1995-04-15', 188, 104, 'C', 0.207, 'USA', 1),
    ('Randal', 'Grichuk', '1991-08-13', 188, 96, 'OF', 0.232, 'USA', 1),
    ('Teoscar', 'Hernandez', '1992-10-15', 188, 92, 'OF', 0.230, 'Dominican Republic', 1),
    ('Tim', 'Anderson', '1993-06-23', 185, 83, 'SS', 0.335, 'USA', 2),
    ('Jose', 'Abreu', '1987-01-29', 190, 115, '1B', 0.284, 'Cuba', 2),
    ('Kevin', 'Kiermaier', '1990-04-22', 185, 95, 'OF', 0.228, 'USA', 3),
    ('Nolan', 'Arenado', '1991-04-16', 188, 97, '3B', 0.315, 'USA', 4),
    ('Mike', 'Trout', '1991-08-07', 188, 106, 'OF', 0.291, 'USA', 5),
    ('Aaron', 'Judge', '1992-04-26', 201, 127, 'OF', 0.272, 'USA', 6),
    ('Giancarlo', 'Stanton', '1989-11-08', 198, 111, 'OF', 0.288, 'USA', 6),
    ('Joey', 'Votto', '1983-09-10', 188, 99, '1B', 0.261, 'Canada', 8);

(3B, SS, 2B, 1B, etc. being the positions)

Here is some sample data for the Team table.

INSERT INTO team
VALUES
    ('Toronto Blue Jays', 'Toronto', 'ON', 'Canada', 'Charlie Montoyo', 'AL', 'Rogers Centre'),
    ('Chicago White Sox', 'Chicago', 'IL', 'USA', 'Rick Renteria', 'AL', 'Guaranteed Rate Field'),
    ('Tampa Bay Rays', 'Tampa Bay', 'FL', 'USA', 'Kevin Cash', 'AL', 'Tropicana Field'),
    ('Colorado Rockies', 'Denver', 'CO', 'USA', 'Bud Black', 'NL', 'Coors Field'),
    ('Los Angeles Angels', 'Anaheim', 'CA', 'USA', 'Joe Maddon', 'AL', 'Angel Stadium of Anaheim'),
    ('New York Yankees', 'New York', 'NY', 'USA', 'Aaron Boone', 'AL', 'Yankee Stadium'),
    ('Pittsburgh Pirates', 'Pittsburgh', 'PA', 'USA', 'Derek Shelton', 'NL', 'PNC Park'),
    ('Cincinnati Reds', 'Cincinnati', 'OH', 'USA', 'David Bell', 'NL', 'Great American Ball Park');

My expected query result should be 2.

So that means that 2 athletes voted for someone who plays the same position as themselves.

I was thinking the query’s top half should be something like:

SELECT COUNT(v.athleteId) 
FROM athlete AS v
JOIN athlete AS a ON v.athleteID = a.votesforId

I am not sure what the WHERE would be.

Any help would be much appreciated.

Answer

You could accomplish this using an inner join with the same table. In the proposed solution, I aliased one voter to represent those who casted votes and the other nominated for those who votes were cast for. The inner join was done on the athletePosition on both table aliases and voter.votesForId= nominated.athleteId before finding the total records.

It was difficult to replicate with the shared schema and data however, I believe the StackOverflow community has shared some valuable insights on how to proceed with these in the comments.

I have shared a db-fiddle that replicates your problem and the proposed solution.

Setup

CREATE TABLE award(
    awardId INT IDENTITY(1, 1),
    awardName VARCHAR(30) NOT NULL,
    awardSponsor VARCHAR(100),
    CONSTRAINT pk_award_awardId PRIMARY KEY (awardId)
);   

CREATE TABLE team(
    teamId INT IDENTITY(1, 1),
    teamName VARCHAR(50) NOT NULL,
    teamCity VARCHAR(20) NOT NULL,
    teamState CHAR(2),
    teamCountry VARCHAR(6),
    teamManager VARCHAR(50),
    teamLeague CHAR(2) NOT NULL,
    teamStadium VARCHAR(30),
    CONSTRAINT pk_team_teamId PRIMARY KEY (teamId)
);
 
CREATE TABLE athlete(
    athleteId INT IDENTITY(1, 1),
    athleteFirstName VARCHAR(20) NOT NULL,
    athleteLastName VARCHAR(30) NOT NULL,
    athleteDateOfBirth DATE,
    athleteHeight SMALLINT,
    athleteWeight TINYINT,
    athletePosition CHAR(2) NOT NULL,
    athleteBattingAvg DECIMAL(4, 3) DEFAULT 0.000,
    athleteNationality VARCHAR(30),
    votesForId INT,
    teamId INT NOT NULL,
    CONSTRAINT pk_athlete_athleteId PRIMARY KEY (athleteId),
    CONSTRAINT fk_athlete_team FOREIGN KEY(teamId) REFERENCES team (teamId)
);

CREATE TABLE winner(
    winnerId INT IDENTITY(1, 1),
    winnerYear INT NOT NULL,
    athleteId INT NOT NULL,
    awardId INT NOT NULL,
    CONSTRAINT pk_winner_winnerId PRIMARY KEY (winnerId),
    CONSTRAINT fk_winner_athlete FOREIGN KEY (athleteId) REFERENCES athlete (athleteId),
    CONSTRAINT fk_winner_award FOREIGN KEY (awardId) REFERENCES award (awardId)
);
GO

INSERT INTO team
VALUES
    ('Toronto Blue Jays', 'Toronto', 'ON', 'Canada', 'Charlie Montoyo', 'AL', 'Rogers Centre'),
    ('Chicago White Sox', 'Chicago', 'IL', 'USA', 'Rick Renteria', 'AL', 'Guaranteed Rate Field'),
    ('Tampa Bay Rays', 'Tampa Bay', 'FL', 'USA', 'Kevin Cash', 'AL', 'Tropicana Field'),
    ('Colorado Rockies', 'Denver', 'CO', 'USA', 'Bud Black', 'NL', 'Coors Field'),
    ('Los Angeles Angels', 'Anaheim', 'CA', 'USA', 'Joe Maddon', 'AL', 'Angel Stadium of Anaheim'),
    ('New York Yankees', 'New York', 'NY', 'USA', 'Aaron Boone', 'AL', 'Yankee Stadium'),
    ('Pittsburgh Pirates', 'Pittsburgh', 'PA', 'USA', 'Derek Shelton', 'NL', 'PNC Park'),
    ('Cincinnati Reds', 'Cincinnati', 'OH', 'USA', 'David Bell', 'NL', 'Great American Ball Park');

 INSERT INTO athlete (
    athleteFirstName ,
    athleteLastName ,
    athleteDateOfBirth,
    athleteHeight,
    athleteWeight,
    athletePosition,
    athleteBattingAvg,
    athleteNationality,
    teamId,
    votesForId
 )
VALUES 
    ('Vladimir', 'Guererro Jr.', '1999-03-16', 188, 113, '3B', 0.272, 'Canada', 1,2),
    ('Bo', 'Bichette', '1998-03-05', 183, 83, 'SS', 0.311, 'USA', 1,1),
    ('Cavan', 'Biggio', '1995-04-11', 188, 90, '2B', 0.234, 'USA', 1,3),
    ('Travis', 'Shaw', '1990-04-16', 193, 104, '1B', 0.157, 'USA', 1,1),
    ('Danny', 'Jansen', '1995-04-15', 188, 104, 'C', 0.207, 'USA', 1,2),
    ('Randal', 'Grichuk', '1991-08-13', 188, 96, 'OF', 0.232, 'USA', 1,3),
    ('Teoscar', 'Hernandez', '1992-10-15', 188, 92, 'OF', 0.230, 'Dominican Republic', 1,2),
    ('Tim', 'Anderson', '1993-06-23', 185, 83, 'SS', 0.335, 'USA', 2,1),
    ('Jose', 'Abreu', '1987-01-29', 190, 115, '1B', 0.284, 'Cuba', 2,1),
    ('Kevin', 'Kiermaier', '1990-04-22', 185, 95, 'OF', 0.228, 'USA', 3,1),
    ('Nolan', 'Arenado', '1991-04-16', 188, 97, '3B', 0.315, 'USA', 4,1),
    ('Mike', 'Trout', '1991-08-07', 188, 106, 'OF', 0.291, 'USA', 5,1),
    ('Aaron', 'Judge', '1992-04-26', 201, 127, 'OF', 0.272, 'USA', 6,1),
    ('Giancarlo', 'Stanton', '1989-11-08', 198, 111, 'OF', 0.288, 'USA', 6,1),
    ('Joey', 'Votto', '1983-09-10', 188, 99, '1B', 0.261, 'Canada', 8,1);

Proposed Solution

SELECT
   SUM(1) as num_votes
FROM
    athlete voter
INNER JOIN
    athlete nominated ON voter.votesForId = nominated.athleteId AND
                         voter.athletePosition = nominated.athletePosition

Returns:

num_votes
2

db<>fiddle here