CODEWITHSUNDEEP
sqlms-access
Ale
Ale

Reputation: 665

GROUP BY and COUNT different fields

I have this table:

CODE    TYPE
A100    A
A100    B
A100    A
A100    B
A100    C
A200    A
A200    A

First I want to delete the rows with type C, then to keep just one row per code in this way:

CODE    COUNT A COUNT B
A100      2      2
A200      2      0

The first column contains the unique code, the second column counts the number of occurrences of A and the second one counts the occurrences of B. I don’t know how to group and count different fields in the same query and to put the result on the same row.

Thank you!

Upvotes: 1

Views: 69

Answers (1)

juergen d
juergen d

Reputation: 204924

select code, 
       sum(iif(type='A', 1, 0) as countA,
       sum(iif(type='B', 1, 0) as countB
from your_table
group by code

Upvotes: 2

Related Questions