Counting duplicate instances within column in SQL when grouping by another column My DB looks something like the following:

id |  Name  | JOBID
----+--------+------
  1 | Sam    | a
  2 | Sam    | b
  3 | Ali    | b
  4 | Tim    | c
  5 | Tim    | d
  6 | Fin    | a
  7 | Mona   | c
  8 | Sam    | e (edited) e is not a duplicate job
  9 | Sam    | f (edited) f is not a duplicate job
  10| Ali    | x (edited) x is not a duplicate job
  11| Fin    | z (edited) z is not a duplicate job


(assume other jobs are all unique and not double-vote (duplicate)

Basically each person will do a job (marked by JobID) once, but the same job can go to two people (max). I want to group by name, but count the instances of duplicate jobIDs that each person got (in column JOBID). How can I do this in SQL?

*Edits - Sorry for editing in the comment section - this is my first time here. You're right, I had made an error in giving the logic. Jobs are either single vote (goes to only one operator) or double vote (go to two operators - hence show as duplicates in JobID column). Any person may have done hundreds of jobs, but I just want to know how many duplicates (double vote jobs) they did.  TO clarify now:


Sam has done 4 (a,b,e,f) jobs but I want to display 2 since he did 2 double vote jobs (a,b).
Ali has done 2 (b,x) jobs but I want to display 1 since he did 2 double vote jobs (b).
Tim has done 2 jobs (c,d) but I want to display 1 since he did 1 double vote jobs (c).


Basically count of double vote is done within the JobID column, but I want it to reflect when I group by name - which person got how may of these jobs. Hope I made this clearer now :)
This should do what you require (fiddle):

Create table j (tid serial, nom text, jid text);

Insert into j (nom, mid) values
('Sam', 'a'),
('Sam', 'b'),
('Ali', 'b'),
('Tim', 'c'),
('Tim', 'd'),
('Finn', 'a'),
('Mona', 'c'),
('Bill', 'z'), 
('Sam',  'x'),
('Sam',  'y'),
('Finn', 'w');


Then:

With dup_jobs AS 
(
  Select jid from j
  Group by jid
  Having count(1) &gt; 1
)
Select j.nom, count(1)
From j
Join dup_jobs dj
  On j.jid = dj.jid 
Group by j.jid
Order by j.jid


Result:

Ali 1
Finn 1
Mona 1
Sam 2
Tim 1



Note that Bill has no jobs in common with anyone else and therefore doesn't appear. Sam's jobs 'x' and 'y' are not shared with anyone else and similarly aren't counted.

Interesting question (+1). p.s. welcome to the forum! :-) Oh, and BTW, in future could you please provide sample table structures (DDL) and data (DML) in the form of a fiddle - it helps us to help you and if there's only one definitive source for the tables and data, it helps prevent errors and duplication of effort.