If I have a table for example:
How would I create query for the unique count?
I can't imagine why you need it, but this should be close:
id, name, job,
WHEN ROW_NUMBER() OVER (PARTITION BY id,job,name ORDER BY id) = 1
THEN COUNT(*) OVER (PARTITION BY id,job,name)
ELSE 0 END