Group rows on condition PostgreSQL

Question

SELECT t."Column1", t."Column2", CASE WHEN MAX(t."Column3") = 5 THEN 5 END AS "Column3"
FROM "Table" AS t
GROUP BY t."Column1", t."Column2"

I have a table with repeated Column1 and Column2 pairs, let's say this is 'many-to-many' table. Also, I have there one extra integer column - Column3.
What I want is select conditionally grouped rows, like
1). If pair of Column1 and Column2 have several records contains 5 value among others - then it should be grouped into one row with Column3: 5;
2). Else, I don't want rows to be grouped.

How that can be achieved? My query do the first requirement but I have no idea how to implement the second. Is it possible via CTE, or subquery only?

Table data example:

Column1   Column2   Column3
'a'       'b'       1
'a'       'b'       2
'a'       'b'       5
'a'       'c'       1
'a'       'c'       2

Desired result:

Column1   Column2   Column3
'a'       'b'       5
'a'       'c'       1
'a'       'c'       2

GMB · Accepted Answer · 2020-07-16 13:19:54Z

3

One option uses not exists and a correlated subquery:

select t.*
from mytable t
where
    column3 = 5
    or not exists (
        select 1
        from mytable t1
        where 
            t1.column1 = t.column1
            and t1.column2 = t.column2
            and t1.column3 = 5
    )

You can also use window functions:

select column1, column2, column3
from (
    select 
        t.*,
        count(*) filter(where column3 = 5) over(partition by column1, column2) cnt_5
    from mytable t
) t
where column3 = 5 or cnt_5 = 0

answered Jul 16, 2020 at 13:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Organek · Accepted Answer · 2020-07-16 13:17:34Z

1

Does this work?

with has_5 as (
  select distinct column1, column2
    from your_table
   where column3 = 5
)
select column1, column2, 5 as column3
  from has_5
union
select t.column1, t.column2, t.column3
  from your_table t
 where not exists (select 1 from has_5
                    where column1 = t.column1 and column2 = t.column2)
;

answered Jul 16, 2020 at 13:17

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

1 Comment

anatol Over a year ago

not tried this, just satisfied with @GMB solution. thanks!

Collectives™ on Stack Overflow

Group rows on condition PostgreSQL

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related