3 Replies Latest reply: Jun 19, 2007 9:28 PM by 583560 RSS

    Return Count when greater than 1.

    583560
      I need queries such as select count(1), emplemail from client_dataset_detail group by emplemail; to only return rows where count(1) is greater than 1. I'm looking for duplicate values in a set of rows. How can I do that?
        • 1. Re: Return Count when greater than 1.
          583485
          I have to go someplace in 5 mins, so I cannot answer your question completely, but I think I can get you 90% there...

          select place1, place2, place3, place4
          FROM
          (select country as place1, state as place2, city as place3, town as place4, COUNT(*) as counter
          FROM mydb
          GROUP BY country, state, city, town)
          WHERE counter > 1

          I think something like that is what you want. Gotta go, sorry.
          • 2. Re: Return Count when greater than 1.
            SomeoneElse
            SQL> select deptno, count(*)
              2  from emp
              3  group by deptno;

                          DEPTNO             COUNT(*)
            -------------------- --------------------
                              30                    6
                              20                    5
                              10                    3

            SQL> select deptno, count(*)
              2  from emp
              3  group by deptno
            4 having count(*) > 3;

                          DEPTNO             COUNT(*)
            -------------------- --------------------
                              30                    6
                              20                    5
            • 3. Re: Return Count when greater than 1.
              583560
              This is what I was looking for. It's been a few years since I've had to do this, and as a Java developer rather than a DBA it doesn't take long to forget.

              Thanks so much!

              Ray