Count average from another MySQL table

Question

I have 2 tables, one is supplier and one is supplier_feedback - how can I calculate the average rating for each supplier? I currently have this:

SELECT s.premium, s.supplier_id, s.name, s.phone, s.website, 
    s.price_low, s.price_high, s.address1, s.address2, s.town,
    s.county, s.postcode, 
    (SUM( f.rating ) / ( COUNT( f.rating ) -1 )) AS rate, 
    GROUP_CONCAT( REPLACE( t.name, ' ', ',' ) SEPARATOR ',' ) AS tags
FROM suppliers AS s
JOIN suppliers_to_tags AS st ON st.supplier_id = s.supplier_id
JOIN supplier_tags AS t ON t.tag_id = st.tag_id
JOIN supplier_feedback AS f ON s.supplier_id = f.supplier_id
GROUP BY s.supplier_id
HAVING tags LIKE '%HI%'
ORDER BY s.premium DESC
LIMIT 0 , 30

But I get very strange results, which definitely aren't correct.

The supplier_feedback table will have many records, but the first record must be discounted for various reasons.

Answer 1

From looking at the query, it does not seem that you are excluding the first record, you are merely reducing the count by 1, but the sum will still include that record value, correct?

Answer 2

use a subquery to make things simpler, that way you can test the subquery for correctness seperately of all the joins to other tables.

(SELECT supplier_id, AVG(rating) AS avg_rating 
FROM supplier_feedback WHERE feedback_id != x GROUP BY supplier_id)

Assuming that feedback_id (whatever the primary key for the feedback table is) is unchanging you can exclude the record you want to ignore from the subquery, the rest will be averaged.

You can then join with this:

    SELECT s.*, r.avg_rating
    FROM suppliers s
    JOIN 
    (SELECT supplier_id, AVG(rating) AS avg_rating 
    FROM supplier_feedback WHERE feedback_id != x GROUP BY supplier_id) r
    ON s.supplier_id = r.supplier_id

Answer 3

The join on tag is causing rows from feedback to appear multiple times. That causes the average to be way off. You could rewrite the FROM part with a subquery for both the tags and the average. That makes sure their calculations don't interfere:

SELECT
     <other columns>
,    feedback.rating
,    suptags.tags
FROM suppliers AS s
JOIN (
    SELECT 
        st.supplier_id
    ,   GROUP_CONCAT(REPLACE( t.name, ' ', ',') SEPARATOR ',') AS tags
    FROM suppliers_to_tags AS st 
    JOIN supplier_tags AS t ON t.tag_id = st.tag_id
    GROUP BY st.supplier_id
) as suptags ON suptags.supplier_id = s.supplier_id
JOIN (
    SELECT 
        fb1.supplier_id
    ,   AVG(fb1.rating) as rating
    FROM supplier_feedback fb1
    WHERE fb1.feedback_id NOT IN (
        SELECT min(fb2.feedback_id) 
        FROM supplier_feedback fb2
        WHERE fb2.supplier_id = fb1.supplier_id
    )
    GROUP BY fb1.supplier_id
) feedback ON s.supplier_id = feedback.supplier_id

The suptags subquery adds all tags per supplier to a string. The feedback subquery calculates the average rating, excluding the row with the lowest feedback_id.