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!

Subqueries in multiple tables

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

  1. SmiD

    SmiD Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hi, I have 2 tables: machine and work.

    Table:machine

    machine_no downtime location
    A1-100-01 2 A1
    A1-100 1.5 A1
    A1-200 3 A1
    CC3-100-01 0.5 CC3
    CC3-100 1.5 CC3

    Table:work

    machine_no date
    A1-100-01 2/4/14
    A1-100 2/14/14
    A1-200 2/6/14
    CC3-100-01 3/15/14
    CC3-100 3/2/14

    I want the output to be like this:

    machine_no total_downtime month
    A1-100 3.5 (total of A1-100, A1-100-01) 02
    A1-200 3 02

    When location A1 is selected.

    I have managed to come up with this code:

    Code (SQL):
    SELECT machine_no, SUM(downtime) AS total_downtime
    FROM (
      SELECT
             SUBSTR(machine_no, 1,
                 CASE WHEN INSTR(machine_no, '-', 1, 2) = 0 THEN LENGTH(machine_no) ELSE INSTR(machine_no, '-', 1, 2)-1 END
                 ) AS machine_no,
             downtime
      FROM machine
      WHERE location='A1'
    ) InnerQuery
    GROUP BY machine_no

    How do I join table WORK and display the month? I'm using Oracle.

    Thank you.
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello ,

    You can try below query
    Code (SQL):
    SELECT SUBSTR(m.machine_no, 1,
                    CASE WHEN INSTR(m.machine_no, '-', 1, 2) = 0 THEN LENGTH(m.machine_no) ELSE INSTR(m.machine_no, '-', 1, 2)-1 END) machine_no,
                    SUM(m.downtime) total_down_time,
                    TO_CHAR(w.mdate,'Mon-yy') month_year
             FROM   machin m,WORK w
             WHERE m.machine_no=w.machine_no
             AND location='A1'
             GROUP BY  SUBSTR(m.machine_no, 1,
                 CASE WHEN INSTR(m.machine_no, '-', 1, 2) = 0 THEN LENGTH(m.machine_no) ELSE INSTR(m.machine_no, '-', 1, 2)-1 END),
             TO_CHAR(w.mdate,'Mon-yy')
    The output of the above query is
    MACHINE_NO TOTAL_DOWN_TIME MONTH_YEAR

    A1-100 3.5 Feb-14
    A1-200 3 Feb-14

    Here we have considered the year along with the month.
     
    SmiD likes this.
  3. SmiD

    SmiD Active Member

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

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    You can also use Decode like below to get the same resultt...


    Code (SQL):


    SELECT
      MACHINE_NO,
      SUM(DOWNTIME) AS TOTAL_DOWNTIME,
      mac_date
    FROM
      (
        SELECT
          SUBSTR(M.MACHINE_NO, 1,
          DECODE(INSTR(M.MACHINE_NO, '-', 1, 2),0,LENGTH(M.MACHINE_NO),
                  INSTR(M.machine_no, '-', 1, 2)-1)
                ) AS MACHINE_NO,
          M.DOWNTIME,
          to_char(W.MAC_DATE,'MM') AS mac_date
        FROM
          MACHINES M,WORK W
        WHERE
          M.MACHINE_NO=W.MACHINE_NO
          AND  M.location='A1'
      )
      INNERQUERY
    GROUP BY
      machine_no,mac_date


     
     
    SmiD likes this.
  5. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Vicky ,

    Yes Decode is fine.

    Is really inner query required for this.

    Here we should consider the year along with the month Coz if the data is for more than one year then the query will display wrong result.
     
    SmiD and Vicky like this.
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tks for the clarification Hegde....:)


    Code (SQL):


    SELECT
      SUBSTR(m.machine_no, 1,
      DECODE(INSTR(M.MACHINE_NO, '-', 1, 2),0,LENGTH(M.MACHINE_NO),
                  INSTR(M.MACHINE_NO, '-', 1, 2)-1)) MACHINE_NO,
      SUM(M.DOWNTIME) TOTAL_DOWN_TIME,
      TO_CHAR(w.mac_date,'Mon-yy') month_year
    FROM
      machines m,
      WORK w
    WHERE
      M.MACHINE_NO=W.MACHINE_NO
      --AND location  ='A1'
    GROUP BY
      SUBSTR(m.machine_no, 1,
      DECODE(INSTR(M.MACHINE_NO, '-', 1, 2),0,LENGTH(M.MACHINE_NO),
                  INSTR(M.MACHINE_NO, '-', 1, 2)-1)),
      TO_CHAR(w.mac_date,'Mon-yy')

     
     
  7. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hegde,

    Isn't possible to display date in "MM" format (after grouping along with year) without going for an outer query.?!.,