I have a query like this:

select
a.date,
a.id,
b.name,
a.data1,
a.data2,
a.data3
from table a,
inner join table b on a.id=b.id.

It returns me data similar to this:

I need the result to look like this if there are rows with repeating ID:

As in take rows with same ID and only show 1 row and sum the DATA results in their own columns

I've tried using listagg but it doesn't seem to work.

select
a.date,
listagg(a.ID, ',') within group (order by null),
b.name,
sum(a.data1),
sum(a.data2),
sum(a.data3)
from table a,
inner join table b on a.id=b.id.

I get results like this:

Am I using listagg wrong or something else is missing?

Thank you!

CodePudding user response：

There is no need for a self join, each of the columns other than id can use an aggregate function:

• For the data1,data2,data3 columns, use SUM for obvious reasons

• For the dt column (date is a reserved word in oracle and should not be used as a column name), use MIN or MAX (depending on your requirements). If you do not use an aggregate function you need to include this column in the GROUP BY clause and it could show multiple rows if different dates exist for the same id.

• For the name column, use MIN or MAX (depending on your requirements). If you do not use an aggregate function you need to include this column in the GROUP BY clause and it could show multiple rows if different names exist for the same id.

-- create test data
CREATE TABLE tablea (dt,  id, name, data1,data2,data3) AS
(
SELECT TO_DATE('06.08.22','DD.MM.YY'),  12345,  'Name1' ,10,    5,  5 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  12345,  'name1' ,15,    3,  2 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  34567,  'name2' ,6, 1,  5 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  45678,  'name3' ,2, 2,  4 FROM DUAL
);


SELECT
  MIN(dt)    AS min_dt,
  id,
  MIN(name)  AS min_name,
  SUM(data1) AS data1,
  SUM(data2) AS data2,
  SUM(data3) AS data3
  FROM
  tablea
 GROUP BY
  id;

MIN_DT              ID MIN_N      DATA1      DATA2      DATA3
----------- ---------- ----- ---------- ---------- ----------
06-AUG-2022      45678 name3          2          2          4
06-AUG-2022      12345 Name1         25          8          7
06-AUG-2022      34567 name2          6          1          5