Getting error '#1111 - Invalid use of group function'

Question

I am getting this error with following query. Please help me correct it.

#1111 - Invalid use of group function

• t_person table contains person details
• tr_category table contains category details
• tr_testimonial table contains rating value details

All tables have personpkid as common field.

SELECT
    p.*,c.*,t.*
FROM
    t_person p
    LEFT JOIN tr_category c ON p.personpkid=c.personpkid
    LEFT JOIN tr_testimonial t ON p.personpkid=t.personpkid
WHERE
    avg(t.ratingvalue)>=5
GROUP BY
    p.personpkid ORDER BY approvedate DESC

Thank you in advance, SG

Answer 1

When you're using a grouping expression to filter results, you need to use HAVING clause. Try this:

SELECT
    p.*,c.*,t.*
FROM
    t_person p
    LEFT JOIN tr_category c ON p.personpkid=c.personpkid
    LEFT JOIN tr_testimonial t ON p.personpkid=t.personpkid
GROUP BY
    p.personpkid ORDER BY approvedate DESC
HAVING
    avg(t.ratingvalue)>=5