SQL query - how to get the lines equal to 1 by count function

Question

i have this table:

Job(j_no,position,company,salary);

and I want to get all the positions that only one company offers. What I was trying to do was:

SELECT position, COUNT(*) AS total FROM (SELECT * FROM Job) GROUP BY 
position;

and i get for example:

  **position          / total**
  Director          |   1
  Draw              |   1
  Software Engineer |   2
  electrician       |   2

how can I return only the position that have a 1 total?

i try to do:

 SELECT position, COUNT(*) AS total FROM (SELECT * FROM Job) GROUP BY 
 position WHERE total=1;

but i get error.

Would appreciate help, thank's.

Answer 1

Change WHERE with HAVING:

SELECT position, COUNT(*) AS total 
FROM Job 
GROUP BY position 
HAVING total = 1;

Answer 2

I want to get all the positions that only one company offers.

If you specifically want positions at only one company, then this is not where the total is 1. A single company could have multiple rows for the same position. So:

SELECT position, COUNT(*) AS total
FROM Job
GROUP BY position
HAVING MIN(company) = MAX(company);

If you wanted the detail on such positions, one method is `not exists:

select j.*
from job j
where not exists (select 1
                  from job j2
                  where j2.position = j.position and j2.company <> j.cmopany
                 )