This discussion is archived
8 Replies Latest reply: Feb 6, 2013 9:03 PM by Karthick_Arp RSS

Query on mutiple records

771423 Newbie
Currently Being Moderated
Hi

I have a oracle table ' table_name_1' having following columns

Table - table_name_1

Columns - claim_number, serial_number, start_date, fail_date, last_insert_date, last_update_date

Keys - The table has primary key on claim_number as well as serial_number columns.


Case - There are records of same claim_number having multiple serial_number ( one to many ) and want to display claim_number, serial_number, count to the client.

Sample Data


claim_number serial_number

335678 A012345

335678 A012346

335678 A012347

335678 A012348

335678 A012349


Could someone please help me displaying claim_number, serial_number, count(*) in a single query..?
  • 1. Re: Query on mutiple records
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Mark wrote:
    Hi

    I have a oracle table ' table_name_1' having following columns

    Table - table_name_1

    Columns - claim_number, serial_number, start_date, fail_date, last_insert_date, last_update_date

    Keys - The table has primary key on claim_number as well as serial_number columns.


    Case - There are records of same claim_number having multiple serial_number ( one to many ) and want to display claim_number, serial_number, count to the client.

    Sample Data


    claim_number serial_number

    335678 A012345

    335678 A012346

    335678 A012347

    335678 A012348

    335678 A012349


    Could someone please help me displaying claim_number, serial_number, count(*) in a single query..?
    Maybe
    SELECT    claim_number
    ,         serial_number
    ,         COUNT (*)    AS cnt
    FROM      table_name_1
    GROUP BY  claim_number
    ,         serial_number
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 2. Re: Query on mutiple records
    771423 Newbie
    Currently Being Moderated
    Table Structure:

    CREATE TABLE ORAOWNER.WARRT072
    (
    CLAIM_NUMBER NUMBER(9),
    PRODUCT_SERIAL_NBR VARCHAR2(10) ,
    HOURS NUMBER(6),
    START_DATE DATE,
    FAIL_DATE DATE,
    TIME_STMP DATE,
    USER_ID VARCHAR2(12)
    )

    CREATE UNIQUE INDEX CLM_DET_PRIM ON CLAIM_DETAIL
    *(CLAIM_NUMBER, ONAN_SERIAL_NBR)*


    Use following Insert Satements :

    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900024, 'A920441354', 1318, TO_DATE('05/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930408, 'H11KBSC620', 39, TO_DATE('02/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930408, 'E100122032', 170, TO_DATE('04/26/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930408, 'B100097823', 557, TO_DATE('08/15/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930408, 'K110277576', 20, TO_DATE('06/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900143, 'F100131489', 786, TO_DATE('10/29/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900163, 'G100139584', 320, TO_DATE('05/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/17/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900183, 'B110191420', 64, TO_DATE('10/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (900183, 'H110238328', 0, TO_DATE('06/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (900183, 'H110238327', 0, TO_DATE('06/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (900183, 'H110242423', 0, TO_DATE('06/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900183, 'C110201996', 207, TO_DATE('02/28/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (929046, 'C12K318157', 6, TO_DATE('09/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (900044, 'I100158848', 70, TO_DATE('11/05/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (930407, 'K120417416', 0, TO_DATE('01/21/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (930406, 'D120328352', 0, TO_DATE('01/09/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, FAIL_DATE)
    Values
    (930405, 'H120373565', 0, TO_DATE('01/08/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930403, 'B10I080320', 0, TO_DATE('10/15/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/27/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930402, 'H10KBHW870', 0, TO_DATE('02/28/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930400, 'E11KBQE110', 36, TO_DATE('03/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (894663, 'B09KAYF770', 425, TO_DATE('10/04/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/28/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (894683, 'L10I066070', 86, TO_DATE('08/13/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, START_DATE, FAIL_DATE)
    Values
    (925402, 'B11I081639', TO_DATE('04/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925402, 'A120294955', 0, TO_DATE('07/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925402, 'L10KBMC510', 5, TO_DATE('07/17/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/22/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    COMMIT;
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925402, 'J10KBKF690', 563, TO_DATE('05/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925409, 'B11I065682', 7606, TO_DATE('08/27/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925410, 'C12K322157', 49, TO_DATE('06/21/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/23/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925414, 'G110232511', 1399, TO_DATE('11/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925420, 'G110232512', 1302, TO_DATE('11/29/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925423, 'F11KBQN130', 480, TO_DATE('01/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925426, 'C110195634', 635, TO_DATE('11/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/16/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925429, 'C110195633', 710, TO_DATE('11/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/24/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925433, 'B11KBNK590', 4334, TO_DATE('12/19/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925435, 'A11KBMH060', 2979, TO_DATE('07/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (925436, 'B12I400013', 2, TO_DATE('09/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (926465, 'J11I069149', 1068, TO_DATE('01/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (926704, 'D110205618', 250, TO_DATE('10/29/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (926723, 'A12K296156', 332, TO_DATE('03/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (926743, 'I11T021531', 2, TO_DATE('04/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/13/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (926746, 'I110265356', 20, TO_DATE('03/26/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE)
    Values
    (930399, 'L08I059478', 1, TO_DATE('08/20/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/17/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (930401, 'L10KBLR510', 57, TO_DATE('04/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('01/28/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (930404, 'B11KBNM620', 0, TO_DATE('07/03/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('01/28/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (881999, 'I09KBAZ910', 72, TO_DATE('11/18/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (881999, 'A11KBMP150', 16, TO_DATE('10/06/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/21/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (881999, 'B10I080334', 2976, TO_DATE('03/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/12/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, HOURS, START_DATE, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (881999, 'B10I080342', 815, TO_DATE('04/25/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
    TO_DATE('12/05/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IB881');
    Insert into CLAIM_DETAIL
    (CLAIM_NUMBER, PRODUCT_SERIAL_NBR, FAIL_DATE, TIME_STMP, USER_ID)
    Values
    (881999, 'K100167804', TO_DATE('02/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'OPS$AC962');
    COMMIT;



    Result Should display following


    CLAIM_NUMBER PRODUCT_SERIAL_NBR, COUNT
  • 3. Re: Query on mutiple records
    jeneesh Guru
    Currently Being Moderated
    As per your sample data, all the counts is 1. Is that the output you looking for?

    Can you post the expected output..

    To get count you could use the below query
    select claim_number,product_serial_nbr,count(*) cnt
    from claim_detail
    group by claim_number,product_serial_nbr;
  • 4. Re: Query on mutiple records
    771423 Newbie
    Currently Being Moderated
    No...as per data you will get multiple hits on product_serial_nbr for a single claim_number record

    The query that you are suggeting is not working as it is not giving any results...we need to find out following out put


    CLAIM_NUMBER PRODUCT_SERIAL_NBR COUNT

    12345 A011113 3

    12346 A066778 5

    12347 A099915 2

    Please help me extracting this output
  • 5. Re: Query on mutiple records
    jeneesh Guru
    Currently Being Moderated
    Mark wrote:
    No...as per data you will get multiple hits on product_serial_nbr for a single claim_number record

    The query that you are suggeting is not working as it is not giving any results...we need to find out following out put


    CLAIM_NUMBER PRODUCT_SERIAL_NBR COUNT

    12345 A011113 3

    12346 A066778 5

    12347 A099915 2

    Please help me extracting this output
    The INSERT script you provided gives below data..
    Can you explain, from the below data, how are you getting the above output?
    select *
    from claim_detail;
    
    CLAIM_NUMBER PRODUCT_SERIAL_NBR  HOURS START_DATE FAIL_DATE TIME_STMP USER_ID    
    ------------ ------------------ ------ ---------- --------- --------- ------------
          900024 A920441354           1318 02-MAY-12  04-JUN-12                        
          930408 H11KBSC620             39 29-FEB-12  20-DEC-12                        
          930408 E100122032            170 26-APR-11  04-JUN-12                        
          930408 B100097823            557 15-AUG-11  01-JUN-12                        
          930408 K110277576             20 26-JUN-12  29-JUN-12                        
          900143 F100131489            786 29-OCT-10  29-JUN-12                        
          900163 G100139584            320 12-MAY-11  17-JUN-12                        
          900183 B110191420             64 13-OCT-11  15-JUN-12                        
          900183 H110238328              0            08-JUN-12                        
          900183 H110238327              0            06-JUN-12                        
          900183 H110242423              0            08-JUN-12                        
          900183 C110201996            207 28-FEB-12  06-JUN-12                        
          929046 C12K318157              6 29-SEP-12  07-JAN-13                        
          900044 I100158848             70 05-NOV-11  04-JUN-12                        
          930407 K120417416              0            21-JAN-13                        
          930406 D120328352              0            09-JAN-13                        
          930405 H120373565              0            08-JAN-13                        
          930403 B10I080320              0 15-OCT-11  27-DEC-12                        
          930402 H10KBHW870              0 28-FEB-12  04-DEC-12                        
          930400 E11KBQE110             36 30-MAR-12  20-OCT-12                        
          894663 B09KAYF770            425 04-OCT-10  28-JUN-12                        
          894683 L10I066070             86 13-AUG-11  26-MAY-12                        
          925402 B11I081639                11-APR-12  11-APR-12                        
          925402 A120294955              0 07-JUL-12  26-NOV-12                        
          925402 L10KBMC510              5 17-JUL-12  22-OCT-12                        
          925402 J10KBKF690            563 20-MAY-12  12-OCT-12                        
          925409 B11I065682           7606 27-AUG-12  10-OCT-12                        
          925410 C12K322157             49 21-JUN-12  23-AUG-12                        
          925414 G110232511           1399 29-NOV-11  12-AUG-12                        
          925420 G110232512           1302 29-NOV-11  12-AUG-12                        
          925423 F11KBQN130            480 20-JAN-12  13-AUG-12                        
          925426 C110195634            635 20-NOV-11  16-AUG-12                        
          925429 C110195633            710 20-NOV-11  24-AUG-12                        
          925433 B11KBNK590           4334 19-DEC-11  26-AUG-12                        
          925435 A11KBMH060           2979 20-JUL-12  10-NOV-12                        
          925436 B12I400013              2 20-SEP-12  13-OCT-12                        
          926465 J11I069149           1068 05-JAN-12  10-SEP-12                        
          926704 D110205618            250 29-OCT-12  05-DEC-12                        
          926723 A12K296156            332 09-MAR-12  05-DEC-12                        
          926743 I11T021531              2 09-APR-12  13-APR-12                        
          926746 I110265356             20 26-MAR-12  11-MAY-12                        
          930399 L08I059478              1 20-AUG-12  17-OCT-12                        
          930401 L10KBLR510             57 01-APR-11  03-DEC-12 28-JAN-13 IB881        
          930404 B11KBNM620              0 03-JUL-12  30-OCT-12 28-JAN-13 IB881        
          881999 I09KBAZ910             72 18-NOV-10  02-OCT-12 05-DEC-12 IB881        
          881999 A11KBMP150             16 06-OCT-11  21-SEP-12 05-DEC-12 IB881        
          881999 B10I080334           2976 07-MAR-11  12-OCT-12 05-DEC-12 IB881        
          881999 B10I080342            815 25-APR-11  10-OCT-12 05-DEC-12 IB881        
          881999 K100167804                           10-FEB-12 11-APR-12 OPS$AC962    
    
     49 rows selected 
  • 6. Re: Query on mutiple records
    Raghav.786 Newbie
    Currently Being Moderated
    Hi Mark, Are you looking for output something like this..
    select t.claim_number,
           t.product_serial_nbr,
           count(1) over(partition by t.claim_number) cnt
      from CLAIM_DETAIL t
     order by t.claim_number;
    CLAIM_NUMBER     PRODUCT_SERIAL_NBR     CNT
    881999     B10I080342     5
    881999     B10I080334     5
    881999     A11KBMP150     5
    881999     I09KBAZ910     5
    881999     K100167804     5
    894663     B09KAYF770     1
    894683     L10I066070     1
    900024     A920441354     1
    900044     I100158848     1
    900143     F100131489     1
    900163     G100139584     1
    900183     H110242423     5
    900183     H110238327     5
    900183     H110238328     5
    900183     B110191420     5
    900183     C110201996     5
    925402     L10KBMC510     4
    925402     J10KBKF690     4
    925402     A120294955     4
    925402     B11I081639     4
    925409     B11I065682     1
    925410     C12K322157     1
    925414     G110232511     1
    925420     G110232512     1
    925423     F11KBQN130     1
    925426     C110195634     1
    925429     C110195633     1
    925433     B11KBNK590     1
    925435     A11KBMH060     1
    925436     B12I400013     1
    926465     J11I069149     1
    926704     D110205618     1
    926723     A12K296156     1
    926743     I11T021531     1
    926746     I110265356     1
    929046     C12K318157     1
    930399     L08I059478     1
    930400     E11KBQE110     1
    930401     L10KBLR510     1
    930402     H10KBHW870     1
    930403     B10I080320     1
    930404     B11KBNM620     1
    930405     H120373565     1
    930406     D120328352     1
    930407     K120417416     1
    930408     E100122032     4
    930408     B100097823     4
    930408     K110277576     4
    930408     H11KBSC620     4
  • 7. Re: Query on mutiple records
    771423 Newbie
    Currently Being Moderated
    perfect..I think we are almost there

    This query is working but can we also add a filter having count(*) > 1 in the same query ?

    I would really appeciate your help

    Thanks

    Mark
  • 8. Re: Query on mutiple records
    Karthick_Arp Guru
    Currently Being Moderated
    Mark wrote:
    perfect..I think we are almost there

    This query is working but can we also add a filter having count(*) > 1 in the same query ?

    I would really appeciate your help

    Thanks

    Mark
    Just wrap it up with a outer query
    select *
      from (
    select t.claim_number,
           t.product_serial_nbr,
           count(1) over(partition by t.claim_number) cnt
      from CLAIM_DETAIL t
           )
     where cnt > 1
     order by claim_number

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points