Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    31

    Unanswered: Selecting records with particular count

    Hi,

    I was wondering if there was a way to select records where the count of a particular value of the record is 2.

    For example, for table employees:
    fname, lname, department,
    a, b, marketing
    c, d, marketing
    e, f, sales
    g,h, sales
    h, i, sales
    j, k, IT
    l, m, IT

    I want to be able to select all records where the number of employees in the department is 2 (in this case, all records where department = marketing and IT).

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Selecting records with particular count

    Use HAVING COUNT(*) = 2

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Need all columns ? Use analytics:

    SQL> create table t (department varchar2(10), int number);

    Table created.

    SQL> insert into t values ('IT', 0);

    1 row created.

    SQL> insert into t values ('IT', 1);

    1 row created.

    SQL> insert into t values ('X',2);

    1 row created.

    SQL> select department, int from (
    2 select t.*, count(*) over (partition by department) c
    3 from t)
    4* where c = 2
    SQL> /

    DEPARTMENT INT
    ---------- ----------
    IT 0
    IT 1

    hth
    al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •