SQLize Online / PHPize Online  /  SQLtest Online

with total as( select staff_id, day(rental_date) as d, count( case when month(rental_date)= 5 then rental_date end )+ count( case when month(return_date)= 5 then return_date end ) as ret from rental where month(rental_date)= 5 AND YEAR(rental_date) = 2005 group by 1, 2 ) select total.staff_id, first_name, last_name, sum(ret)/ count(ret) as average_operation_count from total join staff s on total.staff_id = s.staff_id group by 1, 2, 3 -- WITH operations AS ( -- -- Операции выдачи -- SELECT -- staff_id, -- DATE(rental_date) AS operation_date, -- 'rental' AS operation_type -- FROM rental -- WHERE YEAR(rental_date) = 2005 AND MONTH(rental_date) = 5 -- UNION ALL -- -- Операции возврата -- SELECT -- staff_id, -- DATE(return_date) AS operation_date, -- 'return' AS operation_type -- FROM rental -- WHERE return_date IS NOT NULL -- AND YEAR(return_date) = 2005 AND MONTH(return_date) = 5 -- ), -- working_days AS ( -- SELECT Count(distinct operation_date) as w_days From operations -- ) -- select -- s.staff_id, -- s.first_name, -- s.last_name, -- count(operation_date) / wd.w_days as average_operation_count -- From staff s -- Join operations o ON o.staff_id = s.staff_id -- cross JOIN working_days wd -- group by -- s.staff_id, -- s.first_name, -- s.last_name, -- wd.w_days