Currently I have a table called test:

How could I get the increase in new positive cases on 2021-11-26 compared to 2021-11-25. The result should show a single number indicating the increment. If there are few new positive cases than yesterday, this number should be negative.

Expected results:

explanation: 1 - 3 = -2

CodePudding user response：

Use conditional aggregation.

SELECT SUM(test_date = '2021-11-26') - SUM(test_date = '2021-11-25') AS increment
FROM table
WHERE test_date IN ('2021-11-25', '2021-11-26')
  AND result = 'positive'

CodePudding user response：

You can use below dynamic query, where you do not need to specify dates, you can simply use current date() and date_diff of current_date() and 1:

select(
select count(result)  from test where result = 'positive' and test_date = current_date())
-
(select count(result)  from test where result = 'positive' and test_date =DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) as increment

Result:

increment
-2