In Postgres SQL how can I use group by and ignore if any row in grouping is null?

Question

I have table with data like this

id group order value
-------------------------
1  1     1     23
2  1     2     34
3  2     1     234
4  2     2     77
5  2     3     102
6  3     1     78
7  3     2     NULL

I want to insert into table so I have one row per group, with the value showing a string of comma-separated values orders based on the order. But I want it ignore any groups that contain a null value.

So the result I want is this

id group value
----------------
1  1     23,34
2  2     234,77,102

But with

SELECT
row_number() over () as id,
group,
string_agg(t1.value, ',' ORDER BY "ORDER"),
FROM  t2
GROUP BY group

I currently have

id group value
----------------
1  1     23,34
2  2     234,77,102
3  3     78

Gordon Linoff · Accepted Answer · 2018-07-13 21:34:31Z

2

Just use a having clause:

SELECT row_number() over () as id, group,
       string_agg(t1.value, ',' ORDER BY "ORDER"),
FROM t2
GROUP BY group
HAVING COUNT(*) = COUNT(t1.value);

answered Jul 13, 2018 at 21:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Paul Taylor Over a year ago

Thanks, thats it, my SQL is a bit rusty

Collectives™ on Stack Overflow

In Postgres SQL how can I use group by and ignore if any row in grouping is null?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related