3 Replies Latest reply: May 10, 2012 10:52 AM by 906512 RSS

    Pulling records where number of records for unique ID >= 6

    906512
      I have a table that contains address information for everyone in the system. It has numerous fields, though I've only included a few in the create table query below for the sake of brevity. The PIDM uniquely identifies each record as belonging to a particular person in the database. A person can have multiple addresses in the table, though we normally do not allow them to have more than one active address of a particular ATYP_CODE. Again, I am doing this here for the sake of brevity. What I need to do is pull all the records for each PIDM, but only where there are >= 6 records per PIDM. The user doesn't care if the data are pivoted (I can do that part if needed). Pulling the actual data isn't the issue. I just need a little help figuring out how to get only the records of PIDMs with six or more records in the table. So, from the example data below, the records for PIDM 12345 and 34567 are the ones that should be in the output, but the ones from PIDM 23456 should not.
      DROP TABLE SPRADDR;
      
      CREATE TABLE SPRADDR
      (PIDM              NUMBER(8),
       ATYP_CODE     VARCHAR2(2 CHAR),
       STREETLINE1   VARCHAR2(60 CHAR),
       CITY              VARCHAR2(60 CHAR),
       STATE              VARCHAR2(2 CHAR),
       ZIP              VARCHAR2(10));
      
      INSERT INTO SPRADDR VALUES (12345,'PR','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (12345,'MA','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (12345,'BU','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (12345,'PR','2 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (12345,'MA','3 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (12345,'PR','4 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (23456,'PR','1 MAIN','KENT','OH','44240');
      INSERT INTO SPRADDR VALUES (23456,'MA','1 MAIN','KENT','OH','44240');
      INSERT INTO SPRADDR VALUES (23456,'BU','1 MAIN','KENT','OH','44240');
      INSERT INTO SPRADDR VALUES (34567,'PR','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'MA','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'BU','1 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'PR','2 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'MA','3 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'PR','4 MAIN','CANFIELD','OH','44406');
      INSERT INTO SPRADDR VALUES (34567,'PR','6 MAIN','CANFIELD','OH','44406');
      
      COMMIT;
      I'd greatly appreciate any help you might be able to provide. I'm sure this is easy, but what I've done so far has not worked and I'm not including the code I tried because it's totally cockeyed and not working at all.

      Thanks,
      Michelle Craig
      Data Coordinator
      Admissions Operations and Transfer Systems
      Kent State University
        • 1. Re: Pulling records where number of records for unique ID >= 6
          Solomon Yakobson
          PIDM 12345 and 34567 are the ones that should.
          Why 12345? It is repeated 6 times where you asked for > 6. Anyway:
          SQL> select  *
            2    from  (
            3           select  s.*,
            4                   count(*) over(partition by pidm) cnt
            5             from  spraddr s
            6          )
            7    where cnt > 6
            8    order by pidm
            9  /
          
                PIDM AT STREETLINE1     CITY                 ST ZIP               CNT
          ---------- -- --------------- -------------------- -- ---------- ----------
               34567 PR 1 MAIN          CANFIELD             OH 44406               7
               34567 MA 1 MAIN          CANFIELD             OH 44406               7
               34567 BU 1 MAIN          CANFIELD             OH 44406               7
               34567 PR 2 MAIN          CANFIELD             OH 44406               7
               34567 MA 3 MAIN          CANFIELD             OH 44406               7
               34567 PR 4 MAIN          CANFIELD             OH 44406               7
               34567 PR 6 MAIN          CANFIELD             OH 44406               7
          
          7 rows selected.
          
          SQL> select  *
            2    from  (
            3           select  s.*,
            4                   count(*) over(partition by pidm) cnt
            5             from  spraddr s
            6          )
            7    where cnt >= 6
            8    order by pidm
            9  /
          
                PIDM AT STREETLINE1     CITY                 ST ZIP               CNT
          ---------- -- --------------- -------------------- -- ---------- ----------
               12345 PR 1 MAIN          CANFIELD             OH 44406               6
               12345 MA 1 MAIN          CANFIELD             OH 44406               6
               12345 BU 1 MAIN          CANFIELD             OH 44406               6
               12345 PR 2 MAIN          CANFIELD             OH 44406               6
               12345 MA 3 MAIN          CANFIELD             OH 44406               6
               12345 PR 4 MAIN          CANFIELD             OH 44406               6
               34567 PR 1 MAIN          CANFIELD             OH 44406               7
               34567 MA 1 MAIN          CANFIELD             OH 44406               7
          
                PIDM AT STREETLINE1     CITY                 ST ZIP               CNT
          ---------- -- --------------- -------------------- -- ---------- ----------
               34567 BU 1 MAIN          CANFIELD             OH 44406               7
               34567 PR 2 MAIN          CANFIELD             OH 44406               7
               34567 MA 3 MAIN          CANFIELD             OH 44406               7
               34567 PR 4 MAIN          CANFIELD             OH 44406               7
               34567 PR 6 MAIN          CANFIELD             OH 44406               7
          
          13 rows selected.
          
          SQL>  
          SY.

          Edited by: Solomon Yakobson on May 10, 2012 10:02 AM
          • 2. Re: Pulling records where number of records for unique ID >= 6
            acrsny
            Or, "Old school":

            select * from spraddr
            where pidm in
            (
            select pidm from
            (
            select pidm,count(*) from spraddr
            group by pidm having count(*) >6
            )
            )
            order by pidm
            /

            SQL> /

            PIDM AT STREETLINE1 CITY ST ZIP
            34567 PR 1 MAIN CANFIELD OH 44406
            34567 MA 1 MAIN CANFIELD OH 44406
            34567 BU 1 MAIN CANFIELD OH 44406
            34567 PR 2 MAIN CANFIELD OH 44406
            34567 PR 4 MAIN CANFIELD OH 44406
            34567 PR 6 MAIN CANFIELD OH 44406
            7 rows selected.
            • 3. Re: Pulling records where number of records for unique ID >= 6
              906512
              Thanks - that was the ticket. I integrated your suggestion into a much larger query and it worked great.

              Thanks again,
              Michelle