Menu
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!
771
Introduction to PostgreSQL GROUP BY clause The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., SUM() to calculate the sum of items or COUNT() to get the number of items in the groups.
The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement to group together those rows in a table that have identical data. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
