Count IDs in Column A that are not repeated in Column B

I have been with this query for two days and read many posts, but still can't figure out how to handle this situation.

My table is like this:

 ------ ------ 
| Type |  ID  |
 ------ ------ 
| A    | 1339 |
| A    | 1156 |
| B    | 1156 |
| A    | 1192 |
| B    | 1214 |
| B    | 1202 |
| C    | 1202 |
| A    | 1207 |
| B    | 1207 |
| C    | 1207 |
| B    | 1241 |
 ------ ------

I need to count how many IDs of B there are, but that ID is not repeated in A.

In detail, two criteria should be reflected:

Criterion 1: How many IDs does B have ONLY in B?

Criterion 2: How many IDs does B have in A and B?

C does not matter in this situation.

My expected result should be something like this:

 --------------- ----------- 
| Ds in A and B |  IDs in B |
 --------------------------- 
|         2     |      4    |
 --------------- -----------

CodePudding user response：

It seems that it can be something like this:

select Count(Id) -- Or if we want distinct Ids: Count(Distinct Id)
  from MyTable
 where Type = 'A'                       -- Id has Type 'A'
   and Id not in (select b.Id           -- Not appears among of type 'B'
                    from MyTable b
                   where b.Type = 'B')

Here we get all Id which are have type A, but not B; to find Ids which have A type only:

select Count(Id) -- Or if we want distinct Ids: Count(Distinct Id)
  from MyTable
 where Type = 'A'
   and Id not in (select b.Id 
                    from MyTable b
                   where b.Type <> 'A')

To get Ids that have both type A and B just change not in into in (or do self join):

select Count(Id) -- Or if we want distinct Ids: Count(Distinct Id)
  from MyTable
 where Type = 'A'
   and Id in (select b.Id 
                from MyTable b
               where b.Type = 'B')

CodePudding user response：

Try using COUNT and DISTINCT. But do not forget a WHERE condition to select B type rows. This is the type of query you get end up with :

SELECT COUNT(DISTINCT ID) FROM table WHERE Type = "B"

CodePudding user response：

how IDs in B equal 4? There are five IDs in the B and tree id in (B and not in A)

select COUNT(DISTINCT ID) as AandB from tTable where Type='B' and ID 
in(select id from tTable where Type='A') 

select COUNT(DISTINCT ID) as B from tTable where Type='B'

select COUNT(DISTINCT ID) as Bnot_inA from tTable where Type='B' and ID not 
in(select id from tTable where Type='A')