CODEWITHSUNDEEP
sqloracle11g
Haz
Haz

Reputation: 41

Group by related question. How to get the unique/distinct values, from a column that is not the grouped by column?

This is the simplified query:

select column1, count(*), emails
from table1 group by column1;

however, the "emails" part would throw an error because the group by is for column1, not for the email column. What I would like is to get all the UNIQUE/DISTINCT emails, so the result of the query would be something like this:

col1 count emails
val1 4 email1 email2 email3
val2 10 email4 email5 email6 email7
val3 2 email8 email9

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

What you want is listagg(distinct):

select column1, count(*),
       listagg(distinct email, ',') within group (order by email)
from table1
group by column1;

However, Oracle does not support distinct with listagg(). One workaround is:

select column1, count(*),
       listagg(case when seqnum = 1 then email end, ',') within group (order by email) as emails
from (select t1.*,
             row_number() over (partition by column1, email order by email) as seqnum
      from table1 t1
     ) t1
group by column1;

Upvotes: 2

Related Questions