I have two tables: TEMPLATE table with unique email template IDs and the name

and the STATUS table which shows emails that have been delivered, are pending or failed. It also has the template ID so I know which email template was used

I want to create a new table that shows:

• unique template ID
• count of status type per template ID (only count if status = 'delivered')
• template type (return 'fra' if template_name is NULL, else 'em')

CodePudding user response：

You can use inline query like below.

select 
template_id,
(select count(*) from Status s where s.Template_id=t.template_id AND 
s.status_type='delivered') deliveredcount,

case when t.template_name is null then 'fra'
else 'em' end as template_type
from template t

Or try below window function with distinct.

select distinct
t.template_id,
count (s.template_Id) over (partition by t.template_id) deliveredcount,

case when t.template_name is null then 'fra'
else 'em' end as template_type
from template t left join
Status s on t.template_id=s.template_Id AND s.status_type='delivered'