CODEWITHSUNDEEP
sqloracle-databasesubquery
Matthew
Matthew

Reputation: 867

SQL Subquery greater than/less than operators

I'm currently trying to simply query jobname and jobid via a GROUP BY function. I employ a subquery with COUNT(jobID) >2. Despite this, the output is still displaying values of 2. I understand I can use HAVING with this, but I am genuinely interested as to why this is not working.

SELECT EmployeeType.jobName, COUNT(Employee.JobID) FROM Employee, EmployeeType WHERE (Employee.jobID = EmployeeType.jobID) AND (SELECT COUNT(Employee.jobID) FROM Employee) > 2 GROUP BY EmployeeType.JobName;

Output: enter image description here

Upvotes: 0

Views: 7945

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

It is not that Michael's answer is wrong, but using commas in the FROM clause should be totally discouraged. So, I'm providing alternative answers.

First, the correct way to write the query in the 21st Century is:

SELECT et.jobName, COUNT(*)
FROM Employee e JOIN
     EmployeeType et
     ON e.jobID = et.jobID
GROUP BY et.JobName
HAVING COUNT(e.jobID) > 2;

Also note the use of table aliases.

If JobName is unique in EmployeeType, then you can use a correlated subquery:

select et.jobName,
       (select count(*)
        from employee e
        where e.jobId = et.jobId
       ) as cnt
from employeeType;

You would then use a subquery to filter for the counts you want.

Upvotes: 1

Michael Gunter
Michael Gunter

Reputation: 12811

Your subquery has no relationship with the outer query. When that subquery is executed, it just queries SELECT COUNT(Employee.jobID) FROM Employee for each record being evaluated. The value of that query is greater than 2, so every record gets included.

If I understand what you're trying to do, you should just use a HAVING clause.

SELECT EmployeeType.jobName, COUNT(Employee.jobID)
FROM Employee, EmployeeType
WHERE Employee.jobID = EmployeeType.jobID
GROUP BY EmployeeType.JobName
HAVING COUNT(Employee.jobID) > 2

Upvotes: 3

Related Questions