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.

score 2 · Answer 1 · answered Feb 23 '19 at 11:02

2

Change WHERE with HAVING:

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

answered Feb 23 '19 at 11:02

forpas

160,666
10
38
76

2

@Liran if it works you may consider accepting the answer – forpas Feb 23 '19 at 11:08

score 2 · Answer 2 · answered Feb 23 '19 at 11:29

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
                 )

This might not matter for OP's data, but this answer is IMHO more correct for what is asked. Otherwise the question should be "all the positions which are offered only once". — Paul Spiegel, Feb 23 '19 at 14:15

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

2 Answers2