See if all entries in the same group are valid types

Question

Consider the following table. Each document ( id) belongs to a group ( group_id).

-----------------------
id    group_id    value
-----------------------
 1       1         A
 2       1         B
 3       1         D
 4       2         A
 5       2         B
 6       3         C
 7       4         A
 8       4         B
 9       4         B
10       4         B
11       4         C
12       5         A
13       5         A
14       5         A
15       6         B
16       6         NULL
17       6         NULL
18       6         D
19       7         NULL
20       8         B

1 / Each document has a value of NULL, A, B, CorD

2 / If the documents in the same group have either a value Aor Ba value, the group is completed

3 / In this case, the desired output will read:

---------------------
group_id    completed
---------------------
   1          0       <== because document 3 = D
   2          1       <== all documents have either A or B as a value
   3          0       <== only one document in the group, value C
   4          1       <== all documents have either A or B as a value
   5          1       <== all documents have value A
   6          0       <== because of NULL values and value D
   7          0       <== NULL
   8          1       <== only one document, value B

Can I request this result set?

Since I'm not very good at SQL, any help would be appreciated!

Answer 1

try it

SELECT [group_id], 
       CASE 
         WHEN Count(CASE WHEN [value] IN ( 'A', 'B' ) THEN 1 END) = Count(*) THEN 1 
         ELSE 0 
       END AS COMPLETED 
FROM   yourtable 
GROUP  BY [group_id]