| ID | type | date | Odertime |
|---|---|---|---|
| 1 | A | 2022-03-02 | 2021-06-23 |
| 2 | A | 2022-03-05 | 2021-06-25 |
| 1 | A | 2022-03-02 | 2022-10-09 |
| 3 | A | 2022-03-06 | 2021-04-05 |
| 2 | A | 2022-03-05 | 2022-02-05 |
| 2 | A | 2022-03-05 | 2022-05-13 |
Any idea on how to make this? Thanks. I want count before and after by columns_date like below:
| ID | type | before | after |
|---|---|---|---|
| 1 | A | 1 | 1 |
| 2 | A | 2 | 1 |
| 3 | A | 1 | 0 |
CodePudding user response：
your question is not totally clear, but I guess you meant "date is after or before Odertime", so something like this:
select
id,
type,
sum(case when date > odertime then 1 else 0 end) as before,
sum(case when date < odertime then 1 else 0 end) as after
from test
group by
id,
type