Calculate a new value after applying group by on some columns for snowflake table

So a non-pivot answer can be:

WITH data AS (
 SELECT *
    FROM VALUES
           ('A','B','D',20,'IND1')
          ,('A','B','D',10,'IND2')
          ,('A','B','E',20,'IND1')
          ,('A','B','E',10,'IND2')
    v( Region, Country, State, ValueX, Indicator)
)


SELECT region, 
    country, 
    state,
    SUM(IFF(Indicator='IND1', ValueX, null)) as ind1,
    SUM(IFF(Indicator='IND2', ValueX, null)) as ind2, 
    ind1/ind2 as valuex,
    'IND3' as indicator
FROM data
GROUP BY 1,2,3;

which can be rolled into a working answer of:

SELECT region, 
    country, 
    state,
    SUM(IFF(Indicator='IND1', ValueX, null))/SUM(IFF(Indicator='IND2', ValueX, null)) as valuex,
    'IND3' as indicator
FROM data
GROUP BY 1,2,3;

You are looking for the pivot functionality as described here.

select region, country, state, ind1/ind2 as valuex, 'IND3' as indicator
from my_table
pivot(sum(valuex) for indicator in ('IND1', 'IND2')) as p (region, country, state, ind1, ind2);