How can I improve this nested MySQL query to avoid redundant WHERE statement?

I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question):

tbl_users:

ID | email (all subscribers to our email list)

tbl_mailings:

ID | mail_title (each marketing email)

tbl_sends:

ID | user_id | mailing_id (each user who was sent each mailing)

tbl_opens:

ID | user_id | mailing_id (each user who opened each mailing)

tbl_actions:

ID | user_id | mailing_id (each user who took the target action on each mailing)

I inherited this query that I have been using to get the open rate and action rate for each mailing:

SELECT subq_opens.numopens/subq_sends.numsends as Open_Rate,
       subq_actions.numactions/subq_sends.numsends as Action_Rate,
FROM
(SELECT COUNT(DISTINCT sends.userID) AS numsends
    FROM tbl_sends WHERE mailing_id = 5694) AS subq_sends,

(SELECT COUNT(DISTINCT opens.userID) AS numopens
    FROM tbl_opens WHERE mailing_id = 5694) AS subq_opens,

(SELECT COUNT(DISTINCT actions.userID) AS numactions
    FROM tbl_actions WHERE mailing_id = 5694) AS subq_actions

Not too elegant, but functional, even though I have to change the mailing ID in multiple places every time.

However, now I need to change that WHERE statement to something a great deal more complicated, and I don't want to have to repeat the WHERE statement so many times.

Is there a way I can reformulate this without the redundant WHERE?

I have tried various nesting schemes, but can't seem to find a way to get it right. I can't change anything about the table structure; that is set in stone. Thank you in advance for your help.

Reformulating your query:

SELECT 
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT 
          ( SELECT COUNT(DISTINCT s.userID) 
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )  
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID) 
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )  
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID) 
            FROM tbl_actions AS a 
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM 
          ( SELECT 
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter   
          ) AS param  
    ) AS cte ;

StackExchange Database Administrators Q#39939, answer score: 5