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

Question

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

score 1 · Answer 1 · answered Jul 04 '22 at 20:27

Here's one option: check whether number of null and not null values per each group is a positive number; if so, return null for that group.

Sample data:

SQL> set null NULL
SQL> with test (cgroup, datacolumn) as
  2    (select 1, null    from dual union all
  3     select 1, null    from dual union all
  4     select 1, 'hello' from dual union all
  5     select 1, null    from dual union all
  6     select 2, 'bye'   from dual union all
  7     select 2, 'sorry' from dual union all
  8     select 3, null    from dual union all
  9     select 3, null    from dual union all
 10     select 3, null    from dual
 11    ),

Query begins here:

 12  temp as
 13    (select cgroup, datacolumn,
 14       sum(case when datacolumn is null then 1 else 0 end) over (partition by cgroup) cnt_null,
 15       sum(case when datacolumn is null then 0 else 1 end) over (partition by cgroup) cnt_not_null
 16     from test
 17    )
 18  select cgroup,
 19    case when cnt_null > 0 and cnt_not_null > 0 then null
 20         else datacolumn
 21    end as datacolumn
 22  from temp;

    CGROUP DATACOLUMN
---------- ---------------
         1 NULL
         1 NULL
         1 NULL
         1 NULL
         2 bye
         2 sorry
         3 NULL
         3 NULL
         3 NULL

9 rows selected.

SQL>

score 1 · Accepted Answer · answered Jul 04 '22 at 20:38

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.

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

2 Answers2