SUM function breaks query with columns containing no numerical values

Question

So at my work we inspect tires, and I'm building a sheet to input each day's inspections so that I can see a monthly view.

I have a query in cell A7 in my "Monthly Report" sheet/tab that works, but it breaks with Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC if one of the columns in the query doesn't contain any numerical values amongst their rows.

My query is =QUERY({'1st'!A7:D;'2nd'!A7:D}, "SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) GROUP BY Col1")

I've read from a similar post I should be using the n() function but haven't been able to get the syntax to work. I'm really pushing my limits on google sheet knowledge so any education/help would be lovely.

A simplified copy of the sheet is below. https://docs.google.com/spreadsheets/d/1bmfV59hfWcCYOlWQmdjRKcKhNl1hj9O_1IaZrGm6fAY/edit?usp=sharing

user_9912209 · Answer

You need use CASE statement inside SUM function to check if value is numeric or not.

SELECT Col1, SUM(CASE WHEN ISNUMERIC(Col2)=1 THEN Col2 ELSE 0.00 END), 
SUM(CASE WHEN ISNUMERIC(Col3)=1 THEN Col3 ELSE 0.00 END),
SUM(CASE WHEN ISNUMERIC(Col4)=1 THEN Col4 ELSE 0.00 END) GROUP BY Col1;

user_15516699 · Answer

Try this:

=QUERY({{'1st'!A7:A,ARRAYFORMULA(N('1st'!B7:B)),ARRAYFORMULA(N('1st'!C7:C)),ARRAYFORMULA(N('1st'!D7:D))};{'2nd'!A7:A,ARRAYFORMULA(N('2nd'!B7:B)),ARRAYFORMULA(N('2nd'!C7:C)),ARRAYFORMULA(N('2nd'!D7:D))}},"SELECT Col1, SUM(Col2), SUM(Col3), SUM(Col4) GROUP BY Col1")

Although this works for ignoring non-numerical values, all blank cells have a 0, which can be fixed by applying a conditional formatting like below (this is solely for hiding the "0" values):

Output:

SUM function breaks query with columns containing no numerical values

2 Answers