Google Sheets Query Language, return 0

Question

I've been having troubling getting the query function to return 0 when the queried data isn't found.

Given the following data...

With each sheet being a different player, I need to count the number of games each played and the outcome of those games.

Some players may be in the same game as each other so the gameIDs are given as a unique identifier so those games are only counted once, that part I have working.

What I'm trying to do now is record the number of wins team A has in each room.

You can see my problem in the above screenshot.

Here is the sample sheet that has the above data.

This is the formula used for the right most example

=QUERY({QUERY(UNIQUE(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 <> '' and Col3 contains 'A'")),"select Col1, 1");QUERY(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 = '' and Col1 <> '' and Col3 contains 'A'"),"select Col1, 1")},"select Col1, sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) 'TeamA Wins', Col1 'Room'")

Anyone able to help?

Answer 1

You may add dummy data into the formula:

=QUERY(data;{QUERY(UNIQUE(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 <> '' and Col3 contains 'A'")),"select Col1, 1");QUERY(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 = '' and Col1 <> '' and Col3 contains 'A'"),"select Col1, 1")},"select Col1, sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) 'TeamA Wins', Col1 'Room'")
       ^^^^

data is a named range, it looks like this:

To make it:

1. paste the data to separate sheet, as shown in picture above.
2. select range A1:B6
3. go to Data → Named range → call it "data"
4. enter the formula and it should work fine now.

Sample file with working formula