how to find duplicates/singles in PL/SQL

Sep 3, 2010 | oracle pl/sql | 2 comments

[sql]SELECT emp, COUNT(emp) AS NumOccurrences
FROM users GROUP BY emp
HAVING ( COUNT(emp) > 1 )[/sql]
also, find only one record:
[sql]SELECT emp
FROM users GROUP BY emp
HAVING ( COUNT(emp) = 1 )[/sql]

2 Comments

  1. Stics
    Stics on 21 May, 2011 at 18:10

    Not sure if count() can only be set on multiple fields in a single select. Maybe try and use a Union (Select U Select) and set the count in both ?

  2. Z
    Z on 20 May, 2011 at 21:04

    How do I run an Update statement against these results, where I set a field other than emp on all records that have a count higher than 1?