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
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;
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
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.
You should use the HAVING syntax :
SELECT job, COUNT(*) AS cnt
FROM Employee
GROUP BY job
HAVING cnt = 1;
HAVING clause.
ONLY_FULL_GROUP_BY, it should work.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.
