Writing SQL query to get percentage having certain word/total count of rows in table-CodePudding

I have a table as shown below. I want to return promo % value which is [count of rows having promos value]/[total count of rows]. For ex. below will be 80% [4/5].

Type	Value
Promo	7
Promo	17
Promo	87
Non-Promo	127
Promo	778

I tried using subquery but not able to get the expected value

CodePudding user response：

You may use:

SELECT 100.0 * COUNT(CASE WHEN Type = 'Promo' THEN 1 END) / COUNT(*) AS pct
FROM yourTable;

CodePudding user response：

You can abuse the avg aggregate function:

select avg(case when type ='promo' then 100. else 0 end)
from t

CodePudding user response：

You can try this:

SELECT 
   SUM(CASE WHEN Type = 'Promo' THEN 1.0 ELSE 0.0 END)/COUNT(*) AS PROMO_PERCENTAGE
FROM YOUR_TABLE