See if all records in the same group are of accepted 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 NULL, A, B, C or D

2/ If the documents in the same group all have either A or B as value, the group is completed

3/ In this case, the desired output would 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

IS it possible to query this resultset?

As I am not very experienced in SQL, any help would be appreciated!

Answer 1

Try this

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]