query with group by and count missing zero counts Google sheets

Question

I can get the counts for column E (there are no empty cells in the range) with

=QUERY(Sheet1!D2:H,"select D, count(E) where F = 'person-person' group by D")

But if I add a second where clause

=QUERY(Sheet1!D2:H,"select D, count(E) where F = 'person-person' and H = 'Acquaintence' group by D")

I am missing the zero counts which I need

Thanks for any assistance with this

user_11075129 · Answer

Try this:

=ARRAYFORMULA(QUERY({Sheet1!D2:D,N(Sheet1!F2:F&Sheet1!H2:H="person-personAcquaintence")},"select Col1,SUM(Col2) where Col1<>'' group by Col1"))

SUM()'ing in a query lets you show 0s in a way that counting doesn't really.