The grouping of the two tables?

There are two tables with info, all the info you need to group by day.
But that's under query execution speed is 3 seconds, prompt how competently to implement it:
select DATE(FROM_UNIXTIME(`e`.`inserted_at`)) as `day`,,
SUM(IF(`e`.`type` = 2, `e`.`total_cost`, 0)) as `f_sum`,
SUM(IF(`e`.`type` = 3, `e`.`total_cost`, 0)) as `d_sum`,
SUM(IF(`e`.`type` = 4, `e`.`total_cost`, 0)) as `w_sum`,
(select count(`id_click`) from `click` where `type`=1 and DATE(FROM_UNIXTIME(`inserted_at`)) = `day`) as `click`,
(select count(`id_click`) from `click` where `type`=2 and DATE(FROM_UNIXTIME(`inserted_at`)) = `day`) as `unique_click`
from `event` as `e`
GROUP BY `day`
ORDER BY `day` DESC
LIMIT 10
April 3rd 20 at 18:49
1 answer
April 3rd 20 at 18:51
Solution
To make subqueries in single click in a subquery with grouping (similar to how at the event)
and joinit two subquery by date, something like this:

SELECT
 e.*, c.`click`, c.`unique_click`
 FROM (
SELECT
 DATE(FROM_UNIXTIME(`inserted_at`)) as `day`,
 SUM(IF(`type` = 2, `total_cost`, 0)) as `f_sum`,
 SUM(IF(`type` = 3, `total_cost`, 0)) as `d_sum`,
 SUM(IF(`type` = 4, `total_cost`, 0)) as `w_sum`,
 FROM `event`
 GROUP BY `day`
 ) e
 INNER JOIN (
SELECT
 DATE(FROM_UNIXTIME(`inserted_at`)) as `day`
 SUM(IF(`type` = 1, 1, 0)) as `click`,
 SUM(IF(`type` = 2, 1, 0)) as `unique_click`,
 FROM `click`
 GROUP BY `day`
 ) c ON e.`day` = c.`day`
 ORDER BY e.`day` DESC
 LIMIT 10
;
Geez, thank you. and I already have these crutches did. decided to finally click and event to record the date in the format 21122019 and group by are not it. but your version is prettier and faster than 15ms. thank you - larissa_Jacobs commented on April 3rd 20 at 18:54

Find more questions by tags MySQL