Merging SQL data?

Question

This seems so simple, but I can't seem to figure out how to do it. I have two data sets:

SET1
DATE       | TOTAL1 | TOTAL2 | TOTAL3
1 Jun 2013 | 0      | 0      | 5
2 Jun 2013 | 0      | 0      | 12
3 Jun 2013 | 0      | 0      | 34
4 Jun 2013 | 0      | 0      | 50

SET2
DATE       | TOTAL1 | TOTAL2 | TOTAL3
1 Jun 2013 | 1      | 2      | 0
2 Jun 2013 | 4      | 12     | 0
3 Jun 2013 | 5      | 12     | 0
4 Jun 2013 | 6      | 10     | 0

I want to create a third dataset the merges these two sets into the following:

SET3
DATE       | TOTAL1 | TOTAL2 | TOTAL3
1 Jun 2013 | 1      | 2      | 5
2 Jun 2013 | 4      | 12     | 12
3 Jun 2013 | 5      | 12     | 34
4 Jun 2013 | 6      | 10     | 50

Joining the tables does not work. I need to join them in a way that will add the totals if the dates match up. Any idea how to do this?

It's unclear why you can't just join on date. It's also unclear why your columns are named total. I can't see the SQL you have tried (even if it does not work), nor the Postgres version you are working with ... Please edit your question to make it clear. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 1, 2013 at 14:12

Andomar · Accepted Answer · 2013-07-01 13:49:30Z

5

SELECT
     DATE,
     SUM(TOTAL1) AS TOTAL1,
     SUM(TOTAL2) AS TOTAL2,
     SUM(TOTAL3) AS TOTAL3
FROM
(
     SELECT
          DATE,
          TOTAL1,
          TOTAL2,
          TOTAL3
     FROM
         SET1

     UNION ALL

     SELECT
          DATE,
          TOTAL1,
          TOTAL2,
          TOTAL3
     FROM
         SET2
) SubQueryAlias
GROUP BY
     DATE

edited Jul 1, 2013 at 13:49

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered Jul 1, 2013 at 13:45

George

1,0542 gold badges11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lamak · Accepted Answer · 2013-07-01 13:45:41Z

2

I'm guessing that you want a FULL JOIN:

SELECT  COALESCE(T1.DATE,T2.DATE) AS DATE,
        COALESCE(T1.TOTAL1,0)+COALESCE(T2.TOTAL1,0) AS TOTAL1,
        COALESCE(T1.TOTAL2,0)+COALESCE(T2.TOTAL2,0) AS TOTAL2,
        COALESCE(T1.TOTAL3,0)+COALESCE(T2.TOTAL3,0) AS TOTAL3
FROM Table1 T1
FULL JOIN Table2 T2
    ON T1.DATE = T2.DATE

answered Jul 1, 2013 at 13:45

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Comments

Daniel A. White · Accepted Answer · 2013-07-01 13:42:41Z

0

You still want to do a join but name the columns explicitly, something like this.

SELECT Date, T1.Total1 + T2.Total1 AS TOTAL1, ...
FROM T1 JOIN T2 ON T1.Date = T2.Date

answered Jul 1, 2013 at 13:42

Daniel A. White

192k49 gold badges389 silver badges473 bronze badges

Comments

Olaf H · Accepted Answer · 2013-07-01 13:51:44Z

Use UNION ALL to create 1 table of both sets. Then GROUP BY date and SUM up all totals.

create table set1 (
 d date,
 total1 number,
 total2 number,
 total3 number
);

create table set2 (
 d date,
 total1 number,
 total2 number,
 total3 number
);

insert into set1 (d, total1, total2, total3) values (to_date('01.06.2013', 'dd.mm.yyyy'), 0,0,5);
insert into set1 (d, total1, total2, total3) values (to_date('02.06.2013', 'dd.mm.yyyy'), 0,0,12);
insert into set1 (d, total1, total2, total3) values (to_date('03.06.2013', 'dd.mm.yyyy'), 0,0,34);
insert into set1 (d, total1, total2, total3) values (to_date('04.06.2013', 'dd.mm.yyyy'), 0,0,50);

insert into set2 (d, total1, total2, total3) values (to_date('01.06.2013', 'dd.mm.yyyy'), 1,2,0);
insert into set2 (d, total1, total2, total3) values (to_date('02.06.2013', 'dd.mm.yyyy'), 4,12,0);
insert into set2 (d, total1, total2, total3) values (to_date('03.06.2013', 'dd.mm.yyyy'), 5,12,0);
insert into set2 (d, total1, total2, total3) values (to_date('04.06.2013', 'dd.mm.yyyy'), 6,10,0);

commit;

select d, sum(total1) as total1, sum(total2) as total2, sum(total3) as total3 from (
  select d, total1, total2, total3 from set1
  union all
  select d, total1, total2, total3 from set2
) group by d
order by d;

Iceman · Accepted Answer · 2013-07-01 14:00:01Z

0

You can use something similar:

INSERT INTO set3 (date,Total1,Total2,Total3)
    SELECT s1.date
       ,case when s1.date=s2.date then s1.Total1+s2.Total1 end as Total1
       ,case when s1.date=s2.date then s1.Total2+s2.Total2 end as Total2
       ,case when s1.date=s2.date then s1.Total3+s2.Total3 end as Total3
    FROM set1 s1, set2 s2
    WHERE s1.date=s2.date

It works only when you want to add columns with same date. It will not handle rows where the date is different...

answered Jul 1, 2013 at 14:00

Iceman

214 bronze badges

Collectives™ on Stack Overflow

Merging SQL data?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related