CODEWITHSUNDEEP
sqlpostgresqlgroup-by
anatol
anatol

Reputation: 1762

Group rows on condition PostgreSQL

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

Upvotes: 1

Views: 769

Answers (2)

GMB
GMB

Reputation: 222402

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

Upvotes: 3

Mike Organek
Mike Organek

Reputation: 12494

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)
;

Upvotes: 1

Related Questions