Mysql - group records by date greater than or less than current time now() mysql

Question

I am trying to group records by ID and want to further group them by endDate is greater than or less than current time in mysql. How can I achieve that ? For example :

Rows     ID        endDate
Row 1    AA        2020-01-02
Row 2    AA        2020-03-01

I want the result to be :

AA , 1 (past date), 1 (future date)

Answer 1

I think you want conditional aggregation:

select id,
       sum(enddate < now()) as before_cnt,
       sum(enddate > now()) as after_cnt
from t
group by id;