SQL select data grouped by attr1 with different criteria on attr2 and attr3

I have table with data like this in Access:

attr1 attr2 attr3
A     1     Yes
B     8     Yes
B     6     No
C     4     No
D     5     Yes
D     2     Yes

I need result grouped by attr1, count attr2 and count attr3 only if value is “Yes”:

attr1 attr2 attr3
A     1     1
B     2     1
C     1     0
D     2     2

I would appreciate any help with sql. Thank you very much in advance.

Answer

The expression for the third column is the challenge here.

Consider an IIf expression: IIf(m.attr3='Yes', 1, 0)

That expression will return 1 when the field contains ‘Yes’, and 0 otherwise.

So use that expression in a Sum() function in your query. This query returns what you requested from your sample data.

SELECT
    m.attr1,
    Count(m.attr2) AS CountOfattr2,
    Sum(IIf(m.attr3='Yes', 1, 0)) AS SumOfYes
FROM tblMario AS m
GROUP BY m.attr1;