Search code examples
sqlteradatateradata-sql-assistant

Determine percentage by selecting rows that contains a specific word

I have the following data:

Year  score count
2012 20 grade 2000
2005 20 grade 32
2005 40 grade 428 
2006 60 grade 731
2006 60 grade 472
...

I would need to know what percentage of occasions when the score is 60 grade does it lead to a good score.

I should assume the word grade appears in the text the word grade (so in this case 60 grade). My expected output would be the percentage of rows with only 60 grade based on counts.

How can I select this information by looking at values with 60 grade in Score and determine the percentage?

Solution

  • You could try using a condition aggregation 

    select count(*)
 , sum(case when score like '60%' then 1 else 0  end)  num_x_60
 , (sum(case when score like '60%' then 1 else 0 ) / count(*))*100 perc
from my_table