CODEWITHSUNDEEP
sql
Kalenji
Kalenji

Reputation: 407

SQL combine two records based on one value

Update - work done in SQL-92 I work in SQL reporting tool and trying to combine two records into one. Let's say there as some duplicates were time got split into two values and hence the duplication. Basically any values that are not duplicated should be added

wo---text---time---value 
1----test---5------1
1----test---2------a
3----aaaa---3------1
4----bbbb---4------2

Results

wo---text---time----value
1----test---7--------1a
3----aaaa---3--------1
4----bbbb---4--------2

I tried: SELECT .... FROM ....

GROUP BY wo SUM (time) but that did not even work.

Upvotes: 0

Views: 72

Answers (2)

Ron Ballard
Ron Ballard

Reputation: 701

Set-up:

create table so48345659a
(
    wo     integer,
    text   varchar(4),
    time   integer,
    value  varchar(2)
);

create table so48345659b
(
    wo     integer,
    text   varchar(4),
    time   integer,
    value  varchar(2)
);

insert into so48345659a (wo, text, time, value) values (1, 'test', 5, '1');
insert into so48345659a (wo, text, time, value) values (1, 'test', 2, 'a');
insert into so48345659a (wo, text, time, value) values (3, 'aaaa', 3, '1');
insert into so48345659a (wo, text, time, value) values (4, 'bbbb', 4, '2');

insert into so48345659b (wo, text, time, value) values (1, 'test', 7, '1a');
insert into so48345659b (wo, text, time, value) values (3, 'aaaa', 3, '1');
insert into so48345659b (wo, text, time, value) values (4, 'bbbb', 4, '2');

Union, by default removes duplicates

select wo, text, time, value from so48345659a
union
select wo, text, time, value from so48345659b;

Result:

 wo | text | time | value 
----+------+------+-------
  1 | test |    7 | 1a
  1 | test |    2 | a
  3 | aaaa |    3 | 1
  1 | test |    5 | 1
  4 | bbbb |    4 | 2
(5 rows)

So now run sum on the union 

select
    wo,
    sum(time) as total_time
from
    (
        select wo, text, time, value from so48345659a
        union
        select wo, text, time, value from so48345659b
    ) x
group by
    wo;

Result:

 wo | total_time 
----+------------
  3 |          3
  1 |         14
  4 |          4
(3 rows)

From your supplementary question (22-Jan-2017), I guess you mean that you have one table that contains duplicate rows. Is that right?

If so, it might look like this:

select * from so48345659c;
 wo | text | time | value 
----+------+------+-------
  1 | test |    5 | 1
  1 | test |    2 | a
  3 | aaaa |    3 | 1
  4 | bbbb |    4 | 2
  1 | test |    7 | 1a
  3 | aaaa |    3 | 1
  4 | bbbb |    4 | 2
(7 rows)

So then you get the sum of the times, ignoring duplicate rows, like this:

select
    wo,
    sum(time) as total_time
from
    (
        select distinct wo, text, time, value from so48345659c
    ) x
group by
    wo;

 wo | total_time 
----+------------
  3 |          3
  1 |         14
  4 |          4
(3 rows)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

With just two values, you can do:

select wo, text, sum(time) as time, concat(min(value), max(value)) as value
from t
group by wo, text;

This uses the fact that the standard representation of 1 has a value less than a.

Most databases support string aggregation of some sort (group_concat(), listagg(), and string_agg() are typical functions). You can use one of these for a more general solution.

Upvotes: 0

Related Questions