CODEWITHSUNDEEP
mysqlsqlnested
CallMeBronxy
CallMeBronxy

Reputation: 73

Nested SQL Query

I have problems with the following SQL Query:

SELECT job
FROM (SELECT job, COUNT(*) AS cnt
FROM Employee
GROUP BY job)
WHERE cnt=1

As Result it should only shows all jobs where cnt (count of jobs) equals 1.
When I test the select query above on Fiddle, I get following error :

   Incorrect syntax near the keyword 'WHERE'.

SQLFiddle: http://sqlfiddle.com/#!6/d812a/7

Upvotes: 0

Views: 80

Answers (5)

Loufylouf
Loufylouf

Reputation: 699

You should use the HAVING clause which is done for that kind of thing. Your request will be simply : 

SELECT job FROM Employee GROUP BY job
HAVING COUNT(id)=1

The documentation states that

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

The important thing to note is that contrary to the WHERE clause, you can use aggregate funcitons (like count, max, min ...) in the HAVING clause.

Upvotes: 0

Connected Wanderer
Connected Wanderer

Reputation: 313

You should use the HAVING syntax :

SELECT job, COUNT(*) AS cnt
FROM Employee
GROUP BY job
HAVING cnt = 1;

Upvotes: 0

BOBIN JOSEPH
BOBIN JOSEPH

Reputation: 1022

You forget to add the alias name. Please change the query like this

SELECT job
FROM (SELECT job, COUNT(*) AS cnt
FROM Employee
GROUP BY job) As MyTable
WHERE cnt=1

You should have to give the alias name for the inner query when you are using the select and where clauses outside.

Upvotes: 0

Gaurav Lad
Gaurav Lad

Reputation: 1808

No need to increase complexity by using sub-query when it is not require

SELECT job, count(job)
FROM Employee
GROUP BY job
having count(job)=1;

Upvotes: 1

Sabapathy
Sabapathy

Reputation: 31

You need to provide alias name to the nested query

SELECT A.job
FROM (SELECT job, COUNT(*) AS cnt
FROM Employee
GROUP BY job)A
WHERE A.cnt=1

Upvotes: 1

Related Questions