I have a dataframe:

day            id        value
1               a1       right 
2               a1       right 
2               a1       right 
2               a1       right  
3               a1       right 
4               a1       right 
1               b2       right 
1               b2       right 
2               b2       right 
3               b2       right  

I want to count how many rows there were counted in sum at day 3 for each id group. So desired result is:

day            id       count
3               a1       5 
3               b2       4 

How to do that? I know that in my query I have to write GROUP BY id. But how to count rows in sum at certain day?

CodePudding user response：

This might work

SELECT MAX(day) day, id, COUNT(*) counts
FROM dataframe
WHERE day <= 3
GROUP BY id