Find if the person attended 4 times in any month

xionhack · #1 (**permalink**) 01-08-12, 17:58

Hello. I am making a project on PHP i have 2 tables, a "member" table and an "attendance" table.

On the "member" table I have the fields "member_id", "first_name", "last_name"
On the "attendance" table I have "attendance_id", "date" , "member_id"

What I want to find out is if the person has attended 4 times on "any month". Meaning, check if the person has attended 4 times without me having to specify the month and having to run a query against the month. But 4 times on any month, it cannot be 4 times in 2 months but 4 times in 1 month. Thanks!

r937 · #2 (**permalink**) 01-08-12, 18:25

Quote:

Originally Posted by xionhack

What I want to find out is if the person has attended 4 times on "any month". Meaning, check if the person has attended 4 times without ...

you want to do this for a specific person? which one?

xionhack · #3 (**permalink**) 01-08-12, 21:25

any member. Let's say when member_id = 4

r937 · #4 (**permalink**) 01-08-12, 21:30

Code:

SELECT EXTRACT(YEAR_MONTH FROM `date`) AS yyyymm
     , COUNT(*)
  FROM attendance
 WHERE member_id = 4
GROUP
    BY yyyymm
HAVING COUNT(*) >= 4

xionhack · #5 (**permalink**) 01-08-12, 21:31

Thank you very much! It works!

Internet Services

Web Development

Digital Marketing

Consumer Tech