CODEWITHSUNDEEP
sqloracle-databaseconcatenationjoinlistagg
user7151558
user7151558

Reputation:

Join two tables using LISTAGG or other concatenation functions

I have the following two tables:

Subject
-----------------------------------------------
   ID    | SUBJECT_ID | FIRST_NAME | LAST_NAME 
-----------------------------------------------
2456969  |  0002531   |    Sam     |   White
1371093  |  0002301   |    Tom     |   Andrew
2456840  |  0002529   |    Chris   |   Williams


Subject Countries
--------------------------------
   ID    | ID_OWNER | COUNTRIES  
--------------------------------
2445162  |  2444907 |   303
2457722  |  2457721 |   302
2457650  |  2457649 |   211

I need to use LISTAGG function or some other concatenation function to join the two table together to output the values for "Countries" in a single row.

My current query looks like the following:

sql.append("SELECT s.id, ");
sql.append("     s.subject_id AS subjectId, "); 
sql.append("     s.first_name AS firstName, "); 
sql.append("     s.last_name AS lastName, ");
sql.append("     listagg(sc.countries, ', ') within group (order by sc.countries) AS countriesOfCit ");
sql.append("FROM t_subject s ");
sql.append("     JOIN m_subject_countries sc ");
sql.append("     ON s.id = sc.id_owner ");
sql.append("     group by s.id ");

and I've been getting "ORA-00979: not a GROUP BY expression" error

Upvotes: 1

Views: 2322

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You group by t_subject.id and expect to be able to select all functionally dependent columns, such as t_subject.first_name and t_subject.last_name. This is valid according to the SQL standard.

Oracle, however, doesn't comply with the standard here and requires you to explicitely name all columns in your GROUP BY clause that you want to select unaggregated.

group by s.id, s.subject_id, s.first_name, s.last_name

should solve this issue.

Another solution is to aggregate before joining:

SELECT 
  s.id, 
  s.subject_id AS subjectId,  
  s.first_name AS firstName,  
  s.last_name AS lastName, 
  sc.countriesOfCit 
FROM t_subject s
JOIN 
(
  select
    id_owner,
    listagg(countries, ', ') within group (order by countries) AS countriesOfCit 
  from m_subject_countries
  group by id_owner
) sc ON sc.id_owner = s.id;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142743

All columns (not contained in LISTAGG) should be in the GROUP BY clause: s.id, s.subject_id, s.first_name, s.last_name

Upvotes: 1

Related Questions