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

db2user24 · #1 (**permalink**) 08-03-10, 22:36

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!!

Stealth_DBA · #2 (**permalink**) 08-03-10, 23:47

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

wilsonfv · #3 (**permalink**) 08-04-10, 00:19

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

wilsonfv · #4 (**permalink**) 08-04-10, 00:23

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