Why I’m getting ORA-00979 while trying to use LISTAGG and LEFT JOIN?

I’m trying to group my results so, instead of looking like this:

id  | nome  |  bairro
---------------------
1 . |Test 1 | bairro 1
1 . |Test 1 | bairro 2
2 . |Test 2 | bairro 3

It looks like this:

id  | nome  |  bairro
----------------------
1 . |Test 1 | bairro 1, bairro 2
2 . |Test 2 | bairro 3

The ‘id’ and ‘nome’ are in table 1 and ‘bairro’ is in table 3 in the column ‘nome’.

table 1

id  | nome  | situacao
--------------------
1 . |Test 1 | EM_ATIVIDADE
2 . |Test 2 | EM_ATIVIDADE

table 2

id  | escola (fgk table 1) | bairro (fgk table 3)
-------------------------------------------------
1 . | 2                    | 1
2 . | 2                    | 2

table 3

id  | nome     
---------------
1 . | bairro 1 
2 . | bairro 2 

I’m trying to do it using LISTAGG with the code below:

SELECT table1.nome, table1.id, LISTAGG(table3.nome, ', ') WITHIN GROUP (ORDER BY table3.nome) as "bairro"
FROM table1
LEFT JOIN table2 on table2.escola = table1.id
LEFT JOIN table3 on table3.id = table2.bairro
WHERE table1.situacao = 'EM_ATIVIDADE'
GROUP BY table1.id, table1.nome
ORDER BY table1.id

When I do it, I get the error

ORA-00979: not a group by expression

Can someone help me? I’m a beginner

edit: Already tried adding table1.nome to my group by.

Answer

I think it’s because you’re using an aggregating function which returns a single row, alongside columns which will return multiple rows. So you either need to group every “normal” column in your GROUP BY clause, or apply some aggregating function to the values from the “normal” columns, such as MIN, MAX, SUM, etc.

In your example, it would be something like

SELECT table1.nome, table1.id, LISTAGG(table2.nome, ', ') WITHIN GROUP (ORDER BY table2.nome) as "bairro"
FROM table1
LEFT JOIN table2 on table2.escola = table1.id
LEFT JOIN table3 on table3.id = table2.bairro
WHERE table1.situacao = 'EM_ATIVIDADE'
GROUP BY table1.nome, table1.id
ORDER BY table1.id

or

SELECT MAX(table1.nome), MAX(table1.id), LISTAGG(table2.nome, ', ') WITHIN GROUP (ORDER BY table2.nome) as "bairro"
FROM table1
LEFT JOIN table2 on table2.escola = table1.id
LEFT JOIN table3 on table3.id = table2.bairro
WHERE table1.situacao = 'EM_ATIVIDADE'
ORDER BY table1.id

There is an alternative, which is to use the

OVER (partition BY <column name here>) 

clause, e.g. something like

SELECT table1.nome, table1.id, LISTAGG(table3.nome, ', ') WITHIN GROUP (ORDER BY table3.nome) OVER (PARTITION BY table1.id) as "bairro"
FROM table1
LEFT JOIN table2 on table2.escola = table1.id
LEFT JOIN table3 on table3.id = table2.bairro
WHERE table1.situacao = 'EM_ATIVIDADE'
ORDER BY table1.id

See the Oracle docs for some examples.