Issue with joins — would someone please explain to me what I am doing wrong?

I have two tables member and team with the structure shown below. I am trying to achieve these two results:

2. The number of players on each team plus the total amount of fees collected for each team (ie PlayerFee times number of players).

Schema:

CREATE TABLE member 
(
     MemberID int NOT NULL,
     LastName varchar(20) NOT NULL,
     FirstName varchar(20) NOT NULL,
     MemberType varchar(10) NOT NULL DEFAULT 'Social',
     Phone varchar(20) DEFAULT NULL,
     Handicap int DEFAULT NULL,
     JoinDate date NOT NULL,
     MemberTeam varchar(10) DEFAULT NULL,
     Gender char(1) NOT NULL,
     LifeMember bit(1) NOT NULL DEFAULT b'0',
     PRIMARY KEY (MemberID)
);

INSERT INTO member 
VALUES (118, 'James', 'Melissa', 'Junior', '027-075-0712', 30, '2004-05-08', 'TeamB', 'F', '0');

CREATE TABLE team 
(
     TeamName varchar(10) NOT NULL,
     PracticeNight varchar(10) NOT NULL,
     PlayerFee decimal(10,2) NOT NULL,
     PRIMARY KEY (TeamName)
);

INSERT INTO team 
VALUES ('TeamA', 'Monday', 25.75),
       ('TeamB', 'Tuesday', 16.75); 

I have tried this below code for question 1 but I am not sure that the result I am getting is the right one.

SELECT
    MemberTeam, Handicap, PracticeNight 
FROM
    member 
RIGHT JOIN 
    team ON member.MemberTeam = team.TeamName 
ORDER BY
    PracticeNight;

The question number 1 has been solved but unsure how to use the COUNT function using the JOIN. any help would be much appreciated.

Answer

I like to use TDQD — Test-Driven Query Design — to solve complex SQL queries. These aren’t particularly hard queries, but the idea still allows you to get the right results.

Query 1 — Number of members who attend practice on each day of the week

The query must establish which night each player attends practice, and then count the number of players attending on a given night.

Step 1 — Which night do members attend practice?

SELECT m.Member, t.TeamName, t.PracticeNight
  FROM member AS m
  JOIN team AS t ON m.MemberTeam = t.TeamName

Step 2 — How many players attend practice on each day of the week?

SELECT p.PracticeNight, COUNT(*) AS NumberOfPlayers
  FROM (SELECT m.Member, t.TeamName, t.PracticeNight
          FROM member AS m
          JOIN team AS t ON m.MemberTeam = t.TeamName
       ) AS p
 GROUP BY p.PracticeNight

Note that this does not guarantee any order for the output. Adding ORDER BY p.PracticeNight would yield the data in the sequence Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday because it would be alphabetic order. You’d have to introduce a DayOfWeekNumber for each day of the week, with or example, 0 for Sunday to 6 for Saturday, or 1 for Monday and 7 for Sunday, depending on your preferences.

Note that this query does not produce a zero for days when no team practices, or for days on which the only teams that practice on the day have no members. Fixing that is considerably harder, and requires a list of possible days of the week somewhere so that it’s possible to ensure that all days are represented in the output.

Query 2 — Number of players on each team and total fees collected per team

The ‘total fees’ part is simply the count of the number of players on each team multiplied by the team’s player fee.

Step 1 — Which players are on each team?

SELECT m.Member, t.TeamName
  FROM member AS m
  JOIN team AS t ON m.MemberTeam = t.TeamName

Step 2 — How many players are on each team?

SELECT t.TeamName, COUNT(*) AS NumberOfPlayers
  FROM team AS t
  JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

Step 3 — And the total fees?

SELECT t.TeamName, COUNT(*) AS NumberOfPlayers, COUNT(*) * t.PlayerFee AS TotalFees
  FROM team AS t
  JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

Step 4 — And teams with no players?

Teams with no members will not be included in the output. To get zeros for such teams, use a LEFT OUTER JOIN (which can be abbreviated to LEFT JOIN):

SELECT t.TeamName,
       COUNT(m.MemberID) AS NumberOfPlayers,
       COUNT(m.MemberID) * t.PlayerFee AS TotalFees
  FROM team AS t
  LEFT JOIN member AS m ON t.TeamName = m.MemberTeam
 GROUP BY t.TeamName

The COUNT aggregate does not count NULL values when you specify a column name. When there is no member for a team (TeamA suffers this indignity in the sample data), the LOJ generates a NULL for m.MemberID, so COUNT(m.MemberID) generates 0.

^{Warning: None of the SQL above has been tested; there could be bugs!}