I have the following structure:
MemberID
Year
Month
Day
Everyday, when an user logs in, in the activity will appear something like this:
'1337', '2020', '09', '01'
This thing occurs everyday and what I want to do is to count a member as an active member. If his memberid is displayed in 5 different days, I want to include him to the ,,active members" category. I have a lot of entries and for each memberid I want to do this and count all of them in just one variable.
'1337', '2020', '09', '01'
'1338', '2020', '09', '01'
'1339', '2020', '09', '01'
'1340', '2020', '09', '01'
'1341', '2020', '09', '01'
'1337', '2020', '09', '02'
'1338', '2020', '09', '02'
'1339', '2020', '09', '02'
'1340', '2020', '09', '02'
'1341', '2020', '09', '02'
'1337', '2020', '09', '03'
'1338', '2020', '09', '03'
'1339', '2020', '09', '03'
'1340', '2020', '09', '03'
'1341', '2020', '09', '03'
'1337', '2020', '09', '04'
'1338', '2020', '09', '04'
'1339', '2020', '09', '04'
'1340', '2020', '09', '04'
'1341', '2020', '09', '04'
'1337', '2020', '09', '05'
'1338', '2020', '09', '05'
So what it will count -> 2, because only 1337 and 1338 logged in 5 dif. days.
If you want an overall count of active numbers, you can use two levels of aggegation:
select count(*) cnt
from (select memberid from mytable group by memberid having count(*) > 5) t
The subquery filters users that have more than 5 occurences in the table. Then the outer query just counts them.