How to combine 2 scripts with different conditions

I have 2 scripts with different condition.

Script 1:

select l2_respond_by,count(l2_respond_by) as total
from master_apps_issue
where l2_respond_date BETWEEN '2021-07-01 00:00:00' and '2021-07-09 23:59:00'
group by l2_respond_by;

Script 2:

select l2_return_by,count(l2_return_by) as total
from master_apps_issue
where l2_return_date BETWEEN '2021-07-01 00:00:00' and '2021-07-09 23:59:00'
group by l2_return_by;

Now I need 1 query that generates COUNT results in both and want a result like this.

Thanks.

If LATERAL joins were supported (they are in MySQL 8+):

SELECT
  rrr.name
, SUM(rrr.action = 'respond' ) AS total_l2_respond_by
, SUM(rrr.action = 'return'  ) AS total_l2_return_by
, SUM(rrr.action = 'resolved') AS total_l2_resolved_by
FROM
  master_apps_issue AS mai
  CROSS JOIN LATERAL
  (
    SELECT
      mai.l2_respond_by, 'respond'
    WHERE
      mai.l2_respond_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'

    UNION ALL

    SELECT
      mai.l2_return_by, 'return'
    WHERE
      mai.l2_return_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'

    UNION ALL

    SELECT
      mai.l2_resolved_by, 'resolved'
    WHERE
      mai.l2_resolved_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
  ) AS rrr (name, action)
GROUP BY
  rrr.name
;

The rrr subquery unpivots your set conditionally, returning only the people and events that match a corresponding time constraint. The main SELECT effectively pivots the rows back, aggregating them in the process.

A live db<>fiddle demo is available here.

It isn’t really possible to do this in one scan: the grouping and filtering are completely different.

It seems a full join would be more in order here:

select
  ifnull(resp.l2_respond_by, ret.l2_return_by) as userid,
  resp.total as total_respondby,
  ret.total as total_returnby
from (
    select l2_respond_by, count(l2_respond_by) as total
    from master_apps_issue
    where l2_respond_date BETWEEN '2021-07-01 00:00:00' and '2021-07-09 23:59:00'
    group by l2_respond_by
) as resp
full join (
    select l2_return_by, count(l2_return_by) as total
    from master_apps_issue
    where l2_return_date BETWEEN '2021-07-01 00:00:00' and '2021-07-09 23:59:00'
    group by l2_return_by
) as ret on ret.l2_return_by = resp.l2_respond_by;

There are going to be some assumptions in this answer. As there is no sample dataset with which to test the results, keep these in mind and be sure to adjust the query to suit your situation:

1. the COUNT() functions are using an id of some sort rather than a user_id, as this makes it much easier to verify accuracy
2. the source table is assumed to contain a large number of NULL and duplicate columns, hence the “similar but different” names
3. there is no transaction table for master_apps_issue, making a single query without the derived table all but impossible (This can likely result to historical data changing if the l2_respond_date field is ever updated).

With those notes aside, here is a query that may provide what is being requested:

SELECT tmp.user_id,
       SUM(tmp.responses) as total_l2_respond_by,
       SUM(tmp.returns) as total_l2_return_by,
       SUM(tmp.solutions) as total_l2_resolve_by
  FROM (SELECT l2_respond_by as user_id, COUNT(ticket_id) as responses, 0 as returns, 0 as solutions
          FROM master_apps_issue 
         WHERE l2_respond_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
         GROUP BY l2_respond_by
         UNION ALL
        SELECT l2_return_by as user_id, 0 as responses, COUNT(ticket_id) as returns, 0 as solutions
          FROM master_apps_issue 
         WHERE l2_return_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
         GROUP BY l2_return_by
        UNION ALL
        SELECT l2_resolved_by as user_id, 0 as responses, 0 as returns, COUNT(ticket_id) as solutions
          FROM master_apps_issue 
         WHERE l2_resolved_date BETWEEN '2021-07-01 00:00:00' AND '2021-07-09 23:59:00'
         GROUP BY l2_resolved_by) tmp
 GROUP BY tmp.user_id;