I think, you need the combination of formulas. The answer is:
={QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1"),{"filtered sum";ArrayFormula(IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")),QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1") ,2,0),0))}}
Explanation
It's not hard if you'll take it by parts:
={basic query, {"header"; vlookup(a, help query, 2, 0) }}
Basic query
QUERY({A:C},"select Col1, sum(Col2) where Col1 <>'' group by Col1")
It's simple, I've used Col1, Col2... notation to make it work with any range.
Vlookup
IFERROR(VLOOKUP(UNIQUE(FILTER(A2:A,A2:A<>"")), help query ,2,0),0))
We count sums with criteria (c = 'yes') in the help query.
UNIQUE(FILTER(A2:A,A2:A<>""))
part of the formula gives you a list from column 'a'.
Help query
QUERY({A:C},"select Col1, sum(Col2) where Col3 ='yes' group by Col1")
Here you may enter any conditions what you want. In this case it's Col3 ='yes'
Your second version is correct. For example,
=query(B:G, "select B where " & H2 & " = 'Available'")
works correctly in my spreadsheet when H2 contains a letter such as C or D or E.
Best Answer
Short answer
Put BY column identifier between back quotes,
=unique(query(data_table,"SELECT D, `BY`"))
Explanation
BY is a Google Query Language keyword. From https://developers.google.com/chart/interactive/docs/querylanguage#Identifiers