Alias summing in mysql My query looks like this

select employees.PK_worker,
       concat(employees.name,' ',employees.surname)                    AS name,

       (select coalesce(sum(employees_to_hand.cost),0)
        from   employees_to_hand
        where  employees_to_hand.PK_worker = employees.PK_worker
        and    employees_to_hand.reg_date between @dstart and @dend)   AS to_hand_cost,

       (select coalesce(sum(employees_delegations.cost),0) 
        from   employees_delegations
        where  employees_delegations.PK_worker = employees.PK_worker
        and    employees_delegations.dstart between @dstart and @dend) AS delegation_cost
FROM employees

join (select  @dstart := str_to_date('10/01/2017','%m/%d/%Y'),
              @dend   := str_to_date('11/30/2017','%m/%d/%Y')) d

order by 1;


I am wondering if it is possible to do some basic summing for instance (to_hand_cost + delegation_cost) as simple_sum I assume it might be not possible but repeating the selects again to get the values seems overwhelming. Got any suggestions?

Mysql playground mcve : https://www.db-fiddle.com/f/5FtV24czfAfUiE1dj5EeYt/13
The two inner tables should have INDEX(PK_worker, reg_date, cost) and INDEX(PK_worker, dstart, cost).

Plan A:

The following may help, and it may depend on what version of MySQL you are running:

JOIN ( SELECT str_to_date('10/01/2017','%m/%d/%Y') AS start,
              str_to_date('11/30/2017','%m/%d/%Y') AS end ) AS d


and then use start and end instead of @start and @end.

Rationale:  @variables are sometimes not well optimized.

Plan B:  (But this requires 5.6, perhaps 5.7 - see the EXPLAIN for "auto-key".)

SELECT e.PK_worker,
   concat(e.name,' ',e.surname)   AS name,
    FROM employees AS e
    JOIN ( SELECT PK_worker,
                  coalesce(sum(cost),0) AS to_hand_cost
              FROM employees_to_hand
              WHERE ...
                AND reg_date BETWEEN start AND end
              GROUP BY 1 ) AS ethc
      ON e.PK_worker = ethc.PK_worker
    JOIN ( ... )  AS edc
      ON ...
    ORDER BY 1;

INDEX(reg_date, PK_worker, cost)


Rationale:  Calculating all the costs in one pass over the secondary tables is probably faster than one employee at a time.  Hopefully it is enough faster than the cost of automatically creating INDEX(PK_worker) after generating the 'derived' table.  The different index is a guess as to what would work better for this.

Thanks for dbfiddle, but in this situation, some of the optimizations depend on the size of the table, distribution of the values (esp reg_date, and the version of MySQL).