CODEWITHSUNDEEP
mysqlsql
user3294771
user3294771

Reputation: 23

How to select from a select in MySQL

I have the following data:

https://i.sstatic.net/IH1XU.png

The first table is labeled "Offering" and the second is labeled "Instructor."

I am trying to sum the salaries of all the professors who do not have their instructor ID appear in the offering table.

What I did first was generate a table that has the data I need in it:

select distinct i.InstructorID, i.Salary
from Instructor i
where i.InstructorID NOT IN (select o.InstructorID from Offering o);

Which gives me the desired result here:

https://i.sstatic.net/PJZDF.png

I then want to add these two salaries and have the result displayed in a single salary column. I've tried code like:

$MySQL:> select sum(i.Salary) 
from Instructor i
where i.Salary in ( select distinct i.InstructorID, i.Salary
from Instructor i
where i.InstructorID NOT IN (select o.InstructorID from Offering o));

And get "SQLException: java.sql.SQLException: Operand should contain 1 co." However, I am not sure how to contain the result from my previous query to one column. The way I see it is if I was going to sum all of the salaries that were on the list and I just did a distinct Salary, it would only return three salaries rather than eight salaries, so it would be wrong to do that for my case too.

How would I add the two columns I have generated, and is there an easier way to complete my goal than the method I am using?

Upvotes: 2

Views: 1199

Answers (6)

Jason Heo
Jason Heo

Reputation: 10236

Please test 3 different approaches and choose the best.

NOT IN

SELECT SUM(i.Salary)
FROM Instructor i
WHERE i.InstructorID NOT IN (select o.InstructorID from Offering o);

LEFT JOIN with IS NULL

SELECT SUM(i.Salary)
FROM Instructor i LEFT JOIN Offering o USING(InstructorID)
WHERE o.InstructorID IS NULL;

NOT EXISTS

SELECT SUM(i.Salary)
FROM Instructor i
WHERE NOT EXISTS (SELECT 1 FROM Offering o WHERE o.InstructorID = i.InstructorID);

Upvotes: 0

Kirill Zorin
Kirill Zorin

Reputation: 21

I bet InstructorID is a primary key in Instructor table and I think you don't need "distinct" in select distinct InstructorID, Salary from Instructor.

select sum(Salary)
from Instructor
where InstructorID not in (select InstructorID from Offering);

Upvotes: 0

Cédric Babeix
Cédric Babeix

Reputation: 139

I think this is what you're trying to do :

$MySQL:> select sum(i.Salary) 
from Instructor i
where i.Salary in ( select distinct i.Salary
from Instructor i
where i.InstructorID NOT IN (select o.InstructorID from Offering o));

Upvotes: 0

Prasanth Kumar
Prasanth Kumar

Reputation: 31

You could probably join the two table and then do a group By.

Something like select sum(salary) from a, b where a.instructorid = b.instructorid and .....

Upvotes: 0

Kate
Kate

Reputation: 1576

You should be able to just wrap this in a sub query:

select SUM(Salary)
from (
    select distinct i.InstructorID, i.Salary
    from Instructor i
    where i.InstructorID NOT IN (select o.InstructorID from Offering o)
)

This will work in SQL Server, I think it will work in MYSQL.

Upvotes: 1

user1710796
user1710796

Reputation:

You can use subqueries. They are described by this article

Upvotes: 1

Related Questions