Можно ли совместить два запроса с группировкой?

Question

[Владимир Голубь]

Хочу получить данные по двум дням и с подсчетом количества данных

SELECT 
(
	SELECT COUNT(*)
	FROM  tableX
	WHERE  time >= '2021-06-04 00:00:00' AND time <= '2021-06-04 23:59:59'
	GROUP BY priority
) AS today,
(
	SELECT COUNT(*)
	FROM  tableX
	WHERE time >= '2021-06-03 00:00:00' AND time <= '2021-06-03 23:59:59'
	GROUP BY priority
)  
AS yeasterday
FROM tableX

Хочу получить подобное:

{
today: {
  priority: {
   '1': 100,
   '2': 233
  }
}.
yeasterday: {
 ...
}
}

Answer 1

[Akina]

SELECT priority,
       SUM(time >= '2021-06-04' AND time < '2021-06-05') AS today,
       SUM(time >= '2021-06-03' AND time < '2021-06-04') AS yesterday
FROM  tableX
WHERE time >= '2021-06-03' AND time <= '2021-06-05'
GROUP BY priority

Comments

[Владимир Голубь]

Супер, отличное решение!

Answer 2

[Rsa97]

SELECT `day`, `priority`, `count`
  FROM (
    (SELECT 'today' AS `day`, `priority`, COUNT(*) AS `count`
      FROM `table`
      WHERE `time` >= CURDATE() AND `time` < CURDATE() + INTERVAL 1 DAY
      GROUP BY `priority`)
    UNION ALL (SELECT 'yesterday', `priority`, COUNT(*)
      FROM `table`
      WHERE `time` >= CURDATE() + INTERVAL 1 DAY AND `time` < CURDATE() + INTERVAL 2 DAY
      GROUP BY `priority`)
  ) AS `t`

Comments

[Владимир Голубь]

Не совсем, он объединяет, а нужна статистика по дням

[Владимир Голубь]

В day у меня всегда today

Answer 3

[Владимир Голубь]

Как то так, но может есть вариант лучше

SELECT t1.priority, t1.today, COALESCE(t2.yesterday, 0) AS yesterday
FROM 
    (SELECT priority, COUNT(*) AS 'today' FROM ... WHERE  time >= '2021-06-03 00:00:00' AND time <= '2021-06-03 23:59:59' GROUP BY priority) t1
LEFT JOIN
    (SELECT priority, COUNT(*) AS 'yesterday' FROM ... WHERE time >= '2021-06-03 00:00:00' AND time <= '2021-06-03 23:59:59' GROUP BY priority) t2
ON (t1.priority = t2.priority);