1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Group Parent, Children and Grandchildren Using Oracle SQL

Discussion in 'SQL PL/SQL' started by SmiD, May 5, 2015.

  1. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hi, my task is to calculate the total downtime of machines situated in a particular location. Each machine has parent, children and grandchildren. For eg:

    Location:A1
    Machine no:A1-100, A1-100-01, A1-100-01-001, A1-200, A1-200-01
    (A1-100-01, A1-100-01-001 belongs to A1-100) and (A1-200-01 belongs to A1-200)

    This is my SQL query:
    Code (SQL):
    SELECT machine_no, downtime FROM TABLE_NAME WHERE location='A1'
    The output is:

    machine_no downtime
    A1-100-01 2
    A1-100 1.5
    A1-200 3
    A1-100-01-001 0.5
    A2-200-01 1.5

    My question is how do I group the children and grandchildren to their parent and display the total downtime of that group? I'm sorry if the question is confusing but basically I want the output to be like this:

    machine_no total_downtime
    A1-100 4 (total of A1-100,A1-100-01,A1-100-01-001)
    A1-200 4.5 (total of A1-200,A1-200-01)

    Thank you.
     
  2. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes., The Query below gives Ur required O/P:


    Code (SQL):


    CREATE TABLE MACHINES
    ( MACHINE_NO VARCHAR2(50),DOWNTIME NUMBER
    );

     



    Code (SQL):


    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100-01',2);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100-01',3.5);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100-01-001',0.5);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100-01-001',11.5);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100',3);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100',2);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-100',1.6);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-200',0.7);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-200',8);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-200-01',9);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-200-01',1.3);
    INSERT INTO MACHINES (MACHINE_NO,DOWNTIME) VALUES ('A1-200-01',1.5);

     


    Code (SQL):


    SELECT SUBSTR(MACHINE_NO,1,6) AS MAC_NO,SUM(DOWNTIME) total_down_time FROM MACHINES
    GROUP BY substr(MACHINE_NO,1,6);


    MAC_NO TOTAL_DOWN_TIME
    ------ ---------------
    A1-100            24.1
    A1-200            20.5


     
     
    SmiD likes this.
  3. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    It works! =D...Thank you so much!...Really appreciate it =D...
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    You're Welcome :)
     
  5. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    I have another question...what if the MACHINE_NO has A1-100, CC3-200, CC3-200-01...

    currently the output display CC3-20 instead of CC3-200

    Thank you.
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Code (SQL):


    SELECT SUBSTR(MACHINE_NO,1,INSTR(MACHINE_NO,'-')+3) AS MAC_NO,SUM(DOWNTIME) TOTAL_DOWN_TIME FROM MACHINES
    GROUP BY SUBSTR(MACHINE_NO,1,INSTR(MACHINE_NO,'-')+3);


    MAC_NO                                             TOTAL_DOWN_TIME
    -----------                                     ----------------------
    A1-100                                                        24.1
    CC3-200                                                      11.45
    A1-200                                                        20.5


     
    Hope the length of the 2nd part(A1-100, CC3-200, CC3-200-01...) of the machine_no is constant(3)....
     
    SmiD likes this.
  7. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    It works perfectly :)...But I'm so sorry I just realised this particular machine has R7-1000-01, R7-1100-01, R7-1200-01...

    I'm still new to Oracle SQL and I'm not very familiar with the functions. I'm so sorry..
     
  8. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    SELECT regexp_substr(machine_no, '[^-]+-[^-]+'),
           SUM(downtime)
    FROM TABLE_NAME
    GROUP BY regexp_substr(machine_no, '[^-]+-[^-]+')
     
     
    SmiD likes this.
  9. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Thank you so much for your help :)...but I'm not sure why it is stated 'invalid column name'. regexp_substr is applicable for all Oracle database right?..
     
  10. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Oracle version (not edition) ?
    Can You show exact statement executing ?
     
  11. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Code (SQL):
    SELECT SUBSTR(EQNUM,1,INSTR(EQNUM,'-')+3) AS EQNO, SUM(TOTDOWNTIME) AS TOTAL
    FROM EQUIPMENT
    WHERE (LOCATION=?)
    GROUP BY SUBSTR(EQNUM,1,INSTR(EQNUM,'-')+3)
    This code is based on the above answer. However it only works on string like T2-200-01, CC3-100..it doesnt work on CC3-1000-01

    I believe the code that you gave using regexp_substr will work..but I'm not sure why it's stating invalid column name..
     
  12. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    SmiD... could U show us the table description and the query U're executing..?!
     
  13. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    The Qry wrks fine with me..


    Code (SQL):


    SELECT regexp_substr(machine_no, '[^-]+-[^-]+'),
           SUM(DOWNTIME)
    FROM MACHINES
    GROUP BY regexp_substr(machine_no, '[^-]+-[^-]+');

     
    I'm using oracle 10g..The problem might be with the column_name, which u're using.,
     
    SmiD likes this.
  14. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Will try again :) Thank you so much for your help..really appreciate it :)....