Correct grouping

Question

Good afternoon, I want to know if I use grouping by months correctly, the group itself returns correct data, but it works very slowly. Here is a piece of code, just removed most of the nested SELECTs:

SELECT month(`requests`.`date`) as dt, /** Общая статистика **/ /* Определить: каличество поступивших заявок */ (SELECT COUNT(*) FROM `object_requests` LEFT JOIN `requests` ON `object_requests`.`id_request` = `requests`.`id` WHERE DATE(`requests`.`date`) >= DATE(?) AND DATE(`requests`.`date`) <= DATE(?) AND month(`requests`.`date`)=`dt` ) as `all_requests`, /* Определить: каличество включенных в план заявок */ (SELECT COUNT(*) FROM `object_requests` LEFT JOIN `requests` ON `object_requests`.`id_request` = `requests`.`id` WHERE `requests`.`status` = 0 AND DATE(`requests`.`date`) >= DATE(?) AND DATE(`requests`.`date`) <= DATE(?) AND month(`requests`.`date`)=`dt` ) as `included_requests` FROM `object_requests` LEFT JOIN `requests` ON `object_requests`.`id_request` = `requests`.`id` WHERE DATE(requests.date) >= DATE(?) AND DATE(requests.date) <= DATE(?) GROUP BY month(requests.date) ORDER BY date

Nicolas Chabanovsky ♦ 38.1k 54 gold marks 222 silver marks 438 bronze marks · Accepted Answer · 2011-07-12T13:01:39

Not too sure how in the MySQL dialect. Try this on T-SQL

 SELECT month(`requests`.`date`) as dt, COUNT(*), SUM(CASE WHEN `requests`.`status` = 0 THEN 1 ELSE 0 END) FROM `object_requests` LEFT JOIN `requests` ON `object_requests`.`id_request` = `requests`.`id` WHERE DATE(requests.date) >= DATE(?) AND DATE(requests.date) <= DATE(?) GROUP BY month(requests.date) ORDER BY date

Can you tell me more, and LEFT JOIN is necessary for every request that is attached or is it enough for the main one?

Correct grouping

1 answer 1

More articles: