Search code examples
sqlteradatateradata-sql-assistant

Percentage of cases within a group by year

I have this dataset:

Year   Grade  count 
2010    B2     1
2010    B      478
2010    B1     46
2010    B1     12 
2010    B1     2
2010    B2     612
2010    A1     160
2010    A1     8
...

I would like to determine for each year (in the example there is only 2010) the percentage of A1 and B1 over the total in that year, I.e.

478+46+12+2=538 Over the total of B (B1, B, B2) :1202

In order to get the percentage 45%. Similarly for A. I think I should also use like to distinguish between B and A values. How can I do?

Solution 
  • SEL years,
Score,
,count(0)
,100.0*count(*) / 
Sum(Count(CASE WHEN grade LIKE '_1' THEN 1 end ))   -- A1 & B1
Over (PARTITION BY years, Substr(grade,1,1)) AS pct -- per A/B
From table1
Group by 1,2