Selecting rows with specific text to count and sum

Question

I would need to determine the percentage of specific values by year.

The dataset has values as follows:

Year   Col                         Value 

2012  -20 p,                        12
2012  -20 points, d             20
2012  -20 points, by           24
2012  -20 p, new                32
2012  -30 p,                      1256
2012  -30 points, d             32
2012  -30 points, by           42
2012  -30 p, new               164

There are other years but for the example I selected only 2012. For each year, I would like to determine the percentage as:

• Count of values having points word in the text

Divided by values starting with - 20

Same for case with 30. Expected output for -20 in 2012:

(20+24)/(12+20+24+32)

I have tried as follows

        Select year,
        Col,
        Count(0) as Value
        , 100*count(0)/sum(count(case when Col like ‘-20%points%’ then 1 end) over (partition by year, substr(Col, 1,2))) as pct_20
        /* Same for 40 poin

    ts */
    From table1

Where /* conditions */
Group by 1,2

But I got the error Ordered analytical functions can not be nested.

Answer 1

I think you want conditional aggregation:

select year, substr(col, 1, 2),
       sum(case when col like '%points%' then value end) / sum(value)
from t
group by 1, 2;

Based on your comment:

select year, substr(col, 1, 2),
       sum(case when col like '%points%' then 1.0 end) / count(*)
from t
group by 1, 2;