CODEWITHSUNDEEP
sqlpostgresql
Paul Taylor
Paul Taylor

Reputation: 13210

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

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

Upvotes: 1

Views: 1566

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

Upvotes: 2

Related Questions