SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU21) (KB4557397) - 14.0.3335.7 (X64) 
	Jun 12 2020 20:39:00 
	Copyright (C) 2017 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



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(
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');
8 rows affected

 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);
15 rows affected

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


num_votes


2