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

Isn't working how? Are you getting an error message? Is it giving results you don't expect? If so, what results is it giving? You'll also get better answers if you show sample data. — Josh Eller
– Josh Eller, Commented Feb 20, 2019 at 18:06
@JoshEller You're right, I apologize I just realized I didn't specify how it wasn't working. Editing now. Thank you! — StormsEdge
– StormsEdge, Commented Feb 20, 2019 at 18:07

Josh Eller · Accepted Answer · 2019-02-20 18:28:28Z

1

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

answered Feb 20, 2019 at 18:28

Josh Eller

2,0859 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eric · Accepted Answer · 2019-02-20 18:34:12Z

0

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

answered Feb 20, 2019 at 18:34

Eric

3,2701 gold badge22 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL Query Not Returning As Expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related