CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-query-language
Andre Augusto Alves
Andre Augusto Alves

Reputation: 13

Window Functions in Google Sheets' Query Function

Are there window functions in google sheets query function?

Motivation:

I am currently building a database from this formula

=QUERY('DB'!A2:J,"select C, E, F, sum (J), count(J)  where G = 'Gross Enterprise Subscription' group by C, E, F order by SUM(J) desc label C 'Owner', E 'Country', F 'Region', sum(J) 'Delta ES Increase', count(J) 'Num Enterprises in portfolio'",1)

But i wish I could add a "countifs"-like function side to count (J) where I would put a clause "where J > 0" so I could have simultaneously 'Num Enterprises in portfolio', which is count(j) and 'Num Enterprises in portfolio > 0' which is count(j > 0).

Upvotes: 1

Views: 3367

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY({'DB'!A2:J, IF('DB'!J2:J > 0, 1, )},
 "select Col3,Col5,Col6,sum(Col10),count(Col10),count(Col11)  
  where Col7 = 'Gross Enterprise Subscription' 
  group by Col3,Col5,Col6 
  order by sum(Col10) desc 
  label Col3'Owner',
        Col5'Country',
        Col6'Region',
        sum(Col10)'Delta ES Increase',
        count(Col10)'Num Enterprises in portfolio',
        count(Col11)'Num Enterprises in portfolio > 0'", 1))

Upvotes: 1

Related Questions