Conditional group by (group similar items) in PostgreSQL

Question

I have the following query:

SELECT s.id,
       s.name,
       s.prom,
       s.some_other_field,
       ... many more fields also
FROM mytable s
INNER JOIN (on some other tables etc.)

In the case where there are more than 6 records with the same name (s.name), I want to group these items together as one row (I don't care which other data is grouped into that row, i.e. the first record of that group would be fine).

Is this possible in Postgres? I guess it is a conditional group by in a sense that I want to group the records where the same name appears more than 6 times, whilst returning all the other records as normal.

Any help is much appreciated - thanks!

Answer 1

you can do this:

select * from (

    SELECT s.id,
           s.name,
           s.prom,
           s.some_other_field,
           ... many more fields also,
           row_number() over (partition by s.name order by s.id) as rnk,
           count(*) over (partition by s.name) cnt
    FROM mytable s
    INNER JOIN (on some other tables etc.)

) a
where cnt < 6 or (cnt >=6 and rnk = 1)