Google spreadsheets How to get the output of CASE functionality in sql

Question

Below I have given a dataset and expected output on google sheets. In a normal database, my query would have been Select industry, CASE WHEN change > 0 THEN count(ticker) END) as change, etc. How do I get the expected shown in the picture

I tried multiple Querys, arrayformula and several other functions, none of them has giving all the 3 columns

Answer 1

Here you have an option. First it finds the unique values from industries, and then uses them in BYROW. Inside BYROW, it lists three values: each which is the name, the first COUNTIFS that finds the positive values, and the COUNTIF that counts all the occurences of the industry:

=LET(indus,TOCOL(UNIQUE(B2:B),1),
BYROW(indus,LAMBDA(each,
{each,COUNTIFS(C2:C,">0",B2:B,each),COUNTIF(B2:B,each)})))