Currently I have a table called test:
| id | test_date | result |
|---|---|---|
| 1 | 2021-11-25 | positive |
| 2 | 2021-11-25 | positive |
| 3 | 2021-11-25 | positive |
| 4 | 2021-11-26 | negative |
| 5 | 2021-11-26 | positive |
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:
| increment |
|---|
| -2 |
explanation: 1 – 3 = -2
>Solution :
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'