How can I calculate percentages from total rows and status in sql?

I have a table that contains a list of templates and statuses that I am trying to calculate percentages on.

My schema looks like the following:

Id autonumber
templateId text
personId Id
sentAt Date
status text

SQL SQL to Create DB

CREATE TABLE "test_db" ("id" integer,"templateId" text DEFAULT NULL,"personId" text DEFAULT NULL,"seenAt" text DEFAULT NULL,"status" text DEFAULT NULL, PRIMARY KEY (id));

Sample Data:

| id | templateId | personId | seenAt              | status    |
+----+------------+----------+---------------------+-----------+
|  1 | test 1     | 123      | 17/01/2020 11:16:16 | delivered |
|  2 | test 1     | 456      |                     | rejected  |
|  3 | test 1     | 789      | 17/01/2020 11:16:16 | delivered |
|  4 | test 1     | 345      |                     | delivered |
|  5 | test 2     | 123      |                     | delivered |
|  6 | test 2     | 456      |                     | rejected  |
|  7 | test 2     | 789      | 17/01/2020 11:16:16 | delivered |

What I’m trying to achieve:

| templateId | row_count | delivery_count | delivery_percentage | rejected_count | rejected_count |
+------------+-----------+----------------+---------------------+----------------+----------------+
| test 1     | 4         | 3              | 75%                 | 1              | 25%            |
| test 2     | 3         | 2              | 66.6%               | 1              | 33.3%          |

The query I have so far is the following:

SELECT 
    *, (delivery_count / row_count * 100) AS delivery_percentage
FROM
    (SELECT 
         templateId, 
         COUNT(*) AS row_count,
         (SELECT COUNT(status)
          FROM test_db tb2  
          WHERE tb2.templateId = tb1.templateId AND status = 'delivered'
          GROUP BY tb2.templateId, status) AS delivery_count
      FROM 
          test_db tb1
      GROUP BY 
          templateId)

The problem I have is that my percentages return nothing atm, you can see my sqlfiddle here.

Additionally as you can see I have inner queries for each status, as the number of statuses grow I suspect the query will be hard to read/maintain, is there a more efficient way to do this?

Answer

You can use conditional aggregation:

select templateId,
       sum(status = 'delivered') as num_delivered,
       avg(status = 'delivered') as ratio_delivered,
       sum(status = 'rejected') as num_rejected,
       avg(status = 'rejected') as ratio_rejected
from test_db
group by templateId;

In other databases, you would need to use more standard syntax:

select templateId,
       sum(case when status = 'delivered' then 1 else 0 end) as num_delivered,
       avg(case when status = 'delivered' then 1.0 else 0 end) as ratio_delivered,
       sum(case when status = 'rejected' then 1 else 0 end) as num_rejected,
       avg(case when status = 'rejected' then 1.0 else 0 end) as ratio_rejected
from test_db
group by templateId