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

Google sheet w/data https://docs.google.com/spreadsheets/d/1p0gM4fHWjPf9k40h_GrJjqj8KrxSJDQ-E10LAOgS--Y/edit?usp=sharing

Answer 1

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.