SQL Query Not Returning As Expected

Question

I am trying to return from two relations students who have accepted offers at companies. I populated the tables with a small sample of records in order to test the query and it's not functioning properly. There are 4 scenarios could occur in the application status attribute: 'Offer Accepted', 'Offer Rejected', 'No Offer', 'Pending'.

I am trying to count the number of 'Offer Accepted' and 'Offer Rejected' and output the names of students who have received more than 2 job offers. Why isn't this working? Logically it looks like it should?

I am receiving the wrong count for number of students who received offers. I am receiving names for 4 students at times when I should only be receiving 1. If I add more test cases the test cases continue to behave in the same fashion.

application relation test data:

INSERT INTO applications
VALUES (1, 1001, 'Offer Accepted'), (2, 1001, 'Offer Rejected'), (3, 1001, 'No Offer'), (1, 1000, 'No Offer'), (3, 1000, 'Offer Rejected'), (4, 1000, 'Offer Rejected'), (5, 1000, 'Offer Accepted'), (2, 1003, 'Offer Accepted'), (1, 1002, 'Offer Accepted');

student relation example data:

(1000, 'John Smith', 'Electrical Engineering', 3.6)

• application relation application(JID INT NOT NULL, SID INT NOT NULL, appStatus VARCHAR(100), PRIMARY KEY (JID, SID);

• students relation students(SID INT PRIMARY KEY, SName VARCHAR(100), Smaj VARCHAR(100), gpa FLOAT);

  SELECT SName
  FROM students
  INNER JOIN applications ON applications.SID = students.SID
  GROUP BY students.SName
  HAVING COUNT(appStatus = 'Offer Accepted') + COUNT(appStatus = 'Offer Rejected') > 2
  

Answer 1

This should work.

SELECT s.SID, SName, COUNT(*)
FROM application a
JOIN students s ON a.SID = s.SID
WHERE appStatus IN ('Offer Accepted', 'Offer Rejected')
GROUP BY s.SID, SName
HAVING COUNT(*) > 1

Answer 2

COUNT() just returns the number of records. What's happening is that the equality check appStatus = 'Offer Accepted' is evaluating to a 0 or 1, depending on whether it's true or not. The count function is then just adding up the count of zeros and ones, regardless of the value.

You can use SUM to actually get the number of 1s (i.e., the number of records where this condition is true).

SELECT SName
FROM students
INNER JOIN applications ON applications.SID = students.SID
GROUP BY students.SName
HAVING SUM(appStatus = 'Offer Accepted') + SUM(appStatus = 'Offer Rejected') > 2