Group Function is not allowed here SQL

Question

I have tables like this:

Attendance(offering_id,visitor_id, regstr_date,amount_paid) Offer(offering_id,teacher_id) Teacher(teacher_id,teacher_firstname,teacher_lastname,start_date)

It is possible that one visitor may attend twice or more. I want to retrieve visitor_id, regstr_date,total amount_paid,average amount_paid from attendance where offering id is 30,40 or 50 and teacher's start date is less than any visitor's latest regstr_date and average amount_paid by per visitor is less than 600. My code is as follows:

select 
    distinct(a.visitor_id) as v_id ,
    max(a.regstr_date) as reg_date,
    sum(a.amount_paid) as total_pay,
    count(a.regstr_date) as attendance_count,
    avg(a.amount_paid) as average_paid
from 
    attendance a, teacher t, offer o  
where  
    a.offering_id = o.offering_id 
    and o.teacher_id = t.teacher_id 
    and a.offering_id in ('30', '40', '50') 
    and max(a.regstr_date) > t.start_date
group by 
    a.visitor_id
having 
    avg (a.amount_paid) <= 600;

But it shows group function is not allowed here. If possible could you help me with this please?

Answer 1

This query written for MS-SQL server

select  a.visitor_id as v_id ,
max(a.regstr_date) as reg_date,
sum(a.amount_paid) as total_pay,
count(a.regstr_date) as attendance_count,
avg(a.amount_paid) as average_paid
from attendance a Inner Join  offer o   on a.offering_id = o.offering_id
inner join  teacher t on t.teacher_id = o.teacher_id
where a.offering_id in('30','40','50') 
group by a.visitor_id,t.start_date
having avg (a.amount_paid)<=600 and  max(a.regstr_date)>t.start_date;