Is it possible to combine two queries with grouping?

Question

MySQL

I want to get data for two days and counting the amount of data

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

I want to get something like this:

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

Answer 1

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

Answer 2

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`

Answer 3

Something like this, but maybe there is a better option

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);