Need a SQL select statement to return rows that have the same id in one column and distinct value in another column

I have a table that contains a group number column and a data column:

GROUP DataColumn
1 NULL
1 NULL
1 "hello"
1 NULL
2 "bye"
2 "sorry"
3 NULL
3 NULL
3 NULL

I want to return the string in the DataColunm as long as all rows in that group contain a string (no row is null).

If any row in the group is NULL then I’d like to return all rows in that group with NULL in the DataColumn.

My desired output would be:

GROUP DataColumn
1 NULL
1 NULL
1 NULL (swap "hello" to null since the other values for group 1 are null)
1 NULL
2 "bye"
2 "sorry"
3 NULL
3 NULL
3 NULL

>Solution :

Use COUNT() window function to count all the rows of each GROUP and compare the result to the number of the rows with non-null values:

SELECT "GROUP",
       CASE 
         WHEN COUNT(*) OVER (PARTITION BY "GROUP") = 
              COUNT("DataColumn") OVER (PARTITION BY "GROUP") 
            THEN "DataColumn" 
       END "DataColumn"
FROM tablename;

See the demo.

Leave a Reply