CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
khabibul35
khabibul35

Reputation: 101

QUERY results with count, group, order functions

I have 5 columns of data I want to return from a query, plus a count of the first column.

A couple of other things I want is to only include listings that are active (which is stored by the tag "Include" in Column M) and I want the data to be randomized (I do this by creating a random number generator in column P). Neither of these last 2 should be displayed. The data I wanted to be returned is located in Columns Q, R, S, T, U.

My data looks like this:

M       N       O       P       Q           R       S       T       U
Active  Text    Text    RN      Phone#      ID      Name    Level   Location
Include text    text    0.51423 10000001    1223    Bob     Level 2 Boston
Include text    text    0.34342 10000005    2234    Dylan   Level 3 San Francisco
Exclude text    text    0.56453 10000007    2311    Janet   Level 8 Des Moines
Include text    text    0.23122 10000008    2312    Gina    Level 8 Houston
Include text    text            10000001    1225    Ronda   Level 3 Boston
Include text    text            10000001    1236    Nathan  Level 2 Boston

So, ideally, results would look like:

count Phone#    Phone#      ID      Name    Level       Location
3               10000001    1223    Bob     Level 2     Boston
1               10000005    2234    Dylan   Level 3     San Francisco
1               10000008    2312    Gina    Level 8     Houston

I don't care what ID or Name shows up behind the phone number so long as it's one of the numbers on the list.

Now, I have been able to get the function to work separately (ORDER and COUNT), but can't get both to work in 1 function:

Worked:

=QUERY(Function!M:U, "SELECT count (Q), Q where O = 'Include' group by Q")

=QUERY(Function!M:U, "SELECT Q, R, S, T, U where O = 'Include' ORDER BY P DESC")

Did not work:

=QUERY(Function!M:U, "SELECT count (Q), Q group by Q, R, S, T, U where O = 'Include' group by Q ORDER BY P DESC, R, S, T, U")

=QUERY(Function!M:U, "SELECT count (Q), Q, R, S, T, U group by Q where O = 'Include' group by Q ORDER BY P DESC")

=QUERY(Function!M:U, "SELECT count (Q), Q group by Q where O = 'Include' group by Q ORDER BY P DESC, R, S, T, U")

Maybe someone has an idea of where I'm going wrong with combining the two different types of syntax? Help is much appreciated! :)

Upvotes: 0

Views: 101

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA({"count Phone#", "Phone#", "ID", "Name", "Level", "Location"; 
 QUERY(Function!M3:U, 
 "select count(Q),Q where P is not null group by Q label count(Q)''", 0),
 IFERROR(VLOOKUP(INDEX(QUERY(Function!M3:U, 
 "select Q,count(Q) where P is not null group by Q label count(Q)''", 0),,1),
 QUERY(Function!M3:U, 
 "select Q,R,S,T,U where P is not null order by P desc", 0), {2, 3, 4, 5}, 0))})

0

cell P2: 

=ARRAYFORMULA({"RN"; IF(M3:M="Include", RANDBETWEEN(ROW(A3:A),99^9), )})

Upvotes: 1

Related Questions