Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: how to combine columns from three tables (using group by)

    Hi,

    I have these three tables --

    db2 "select * from table1"
    ID READINGDATE VAL1
    ----------- -------------------------- ------------------------
    1 2010-07-01-00.00.00.000000 200
    1 2010-07-01-01.00.00.000000 500

    2 record(s) selected.

    db2 "select * from table2"

    ID READINGDATE VAL2
    ----------- -------------------------- ------------------------
    1 2010-07-01-00.00.00.000000 100
    1 2010-07-01-01.00.00.000000 200

    2 record(s) selected.

    db2 "select * from table3"

    ID READINGDATE VAL3
    ----------- -------------------------- ------------------------
    1 2010-07-01-00.00.00.000000 150
    1 2010-07-01-01.00.00.000000 100
    1 2010-07-01-00.00.00.000000 300
    1 2010-07-01-01.00.00.000000 200

    4 record(s) selected.



    I want to use group by based on date ( I have the same date repeated in table3) and combine columns from all three and get this result --


    ID READINGDATE VAL1 VAL2 VAL3
    1 2010-07-01-00.00.00.000000 200 100 450
    1 2010-07-01-01.00.00.000000 500 200 300

    Is there a query that will give me this result? thanks!!

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    db2user24, This may be too much work for what you want but it depends on your data.
    Code:
    WITH TAB1 (ID, READINGDATE, VAL1)
      AS (SELECT 1, '2010-07-01-00.00.00.000000', 200 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 1, '2010-07-01-01.00.00.000000', 500 FROM SYSIBM.SYSDUMMY1
         )
       , TAB2 (ID, READINGDATE, VAL2)
      AS (SELECT 1, '2010-07-01-00.00.00.000000', 100 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 1, '2010-07-01-01.00.00.000000', 200 FROM SYSIBM.SYSDUMMY1
         )
       , TAB3 (ID, READINGDATE, VAL3)
      AS (SELECT 1, '2010-07-01-00.00.00.000000', 150 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 1, '2010-07-01-01.00.00.000000', 100 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 1, '2010-07-01-00.00.00.000000', 300 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 1, '2010-07-01-01.00.00.000000', 200 FROM SYSIBM.SYSDUMMY1
         )
    SELECT SUMTAB1.ID, SUMTAB1.READINGDATE, VAL1, VAL2, VAL3
    FROM (SELECT ID, READINGDATE, SUM(VAL1) AS VAL1
          FROM TAB1
          GROUP BY ID, READINGDATE
         ) AS SUMTAB1
           FULL OUTER JOIN
         (SELECT ID, READINGDATE, SUM(VAL2) AS VAL2
          FROM TAB2
          GROUP BY ID, READINGDATE
         ) AS SUMTAB2
           ON     SUMTAB1.ID          = SUMTAB2.ID
              AND SUMTAB1.READINGDATE = SUMTAB2.READINGDATE
           FULL OUTER JOIN
         (SELECT ID, READINGDATE, SUM(VAL3) AS VAL3
          FROM TAB3
          GROUP BY ID, READINGDATE
         ) AS SUMTAB3
           ON     SUMTAB2.ID          = SUMTAB3.ID
              AND SUMTAB2.READINGDATE = SUMTAB3.READINGDATE
    
    ID          READINGDATE                VAL1        VAL2        VAL3       
    ----------- -------------------------- ----------- ----------- -----------
              1 2010-07-01-00.00.00.000000         200         100         450
              1 2010-07-01-01.00.00.000000         500         200         300
    This assumes you may have multiple rows in both table1 and table2 in addition to table3 that needs to be summarized. It also uses a full outer join in case you have rows in one table but not another (and you may need to use COALESCE for missing rows but that depends). Additionally, it assumes ID could be other than 1.

    If you only have to summarize rows in table3 AND you will always have the same date in all 3 tables it could be simplified a little to:
    Code:
    SELECT TAB1.ID, TAB1.READINGDATE, VAL1, VAL2, VAL3
    FROM TAB1
           INNER JOIN
         TAB2
           ON     TAB1.ID          = TAB2.ID
              AND TAB1.READINGDATE = TAB2.READINGDATE
           INNER JOIN
         (SELECT ID, READINGDATE, SUM(VAL3) AS VAL3
          FROM TAB3
          GROUP BY ID, READINGDATE
         ) AS TAB3
           ON     TAB2.ID          = TAB3.ID
              AND TAB2.READINGDATE = TAB3.READINGDATE
    
    ID          READINGDATE                VAL1        VAL2        VAL3       
    ----------- -------------------------- ----------- ----------- -----------
              1 2010-07-01-00.00.00.000000         200         100         450
              1 2010-07-01-01.00.00.000000         500         200         300
    Or you can do away with all the joins and use this:
    Code:
    SELECT ID, READINGDATE, SUM(VAL1) AS VAL1, SUM(VAL2) AS VAL2, SUM(VAL3) AS VAL3
    FROM (SELECT ID, READINGDATE, VAL1, 0 AS VAL2, 0 AS VAL3
          FROM TAB1
          UNION ALL
          SELECT ID, READINGDATE, 0 AS VAL1, VAL2, 0 AS VAL3
          FROM TAB2
          UNION ALL
          SELECT ID, READINGDATE, 0 AS VAL1, 0 AS VAL2, VAL3
          FROM TAB3
         ) AS A
    GROUP BY ID, READINGDATE
    
    ID          READINGDATE                VAL1        VAL2        VAL3       
    ----------- -------------------------- ----------- ----------- -----------
              1 2010-07-01-00.00.00.000000         200         100         450
              1 2010-07-01-01.00.00.000000         500         200         300

  3. #3
    Join Date
    Apr 2009
    Posts
    42
    Perform a group by on table3 first then join to table1, table2 and resultSet of table3.

    select t1.id, t1.readingdate, t1.val1, t2.val2, v3.val3
    from
    table1 as t1,
    table2 as t2,
    (
    select id, reading date, sum(val3) as val3 from table3 group by id, reading date
    ) as v3
    where t1.readingdate = t2.readingdate
    and t1.readingdate = v3.readingdate

  4. #4
    Join Date
    Apr 2009
    Posts
    42
    Perform a group by on table3 first then join to table1, table2 and resultSet of table3.

    select t1.id, t1.readingdate, t1.val1, t2.val2, v3.val3
    from
    table1 as t1,
    table2 as t2,
    (
    select id, reading date, sum(val3) as val3 from table3 group by id, reading date
    ) as v3
    where t1.readingdate = t2.readingdate
    and t1.readingdate = v3.readingdate

  5. #5
    Join Date
    Nov 2007
    Posts
    265
    wow, thanks so much for the replies.. will def try these out.. btw, there can be multiple rows for the same date in table 1 and table 2 as well..

  6. #6
    Join Date
    Apr 2009
    Posts
    42
    It is easy. Group by table1 and table2 separately like the way how table3 is group by , then join the 3 resultsets.

Posting Permissions

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