'Google sheet array mismatch with different query size

We want to create dynamic table by single google sheet query or in single array where each column size is different .

It work when we use individual query for each column separately, but if we aggregate all query
for individual column in a single array then it show array mismatch error ... Because each column individual query return size is different ,that mean arrays with different size in a single array , so it create error..

There are any way to bypass this error...?

Or we need to use individual query in each individual column...?

My sample formula : array with different array size is given below. please let me know , is there any way to bypass array size mismatch problem..

={ QUERY(Leads!A2:P," SELECT P, COUNT(N) WHERE N IS NOT NULL AND N='Contact in Progress' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Contact in Progress' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Rejected / DND' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Rejected / DND' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Made Offer' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Made Offer' "),
QUERY(Leads!A2:P," SELECT COUNT(N) WHERE N IS NOT NULL AND N='Under Contract' AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P LABEL COUNT(N) 'Under Contract' ")}


Solution 1:[1]

Try (add additional criteria)

=query(Leads!A2:P,"SELECT P, count(P) where N is not null GROUP BY P PIVOT N")

with your additionnal conditions, try

=query(Leads!A2:P,"SELECT P, count(P) where N is not null AND P >= DATE """&TEXT(D2,"YYYY-MM-DD")&""" AND P<= DATE """&TEXT(D3,"YYYY-MM-DD")&""" AND C = """&F2&""" GROUP BY P PIVOT N")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1