MySQL ( v5.7.30 ) query AVG of AVGs

Question

So I have these 2 tables:

jobs:

-------------------------------------------------
id        business_id        other_columns
-------------------------------------------------
1         223                 xxxxxx
-------------------------------------------------
1         12                  xxxxxx
-------------------------------------------------

businesses_ratings:

--------------------------------------------------------------------------------------
id   business_id   professional   communication   safety   respectful   dependability        
--------------------------------------------------------------------------------------
1    223           4              2               5        4            3      
--------------------------------------------------------------------------------------
2    223           3              5               2        4            5
--------------------------------------------------------------------------------------
3    223           1              2               5        4            4
--------------------------------------------------------------------------------------

I want to select the jobs of a particular business_id, and append to each job the overall rating of that business_id, computed as AVG((AVG(professional), AVG(communication), AVG(safety), AVG(respectful), AVG(dependability))

Can I achieve this in one query?

LE: I'll append here the query I've tried ( containing also the WHERE clause, maybe it'll help explaining better what I need to achieve. Also the thrown error:

SELECT * FROM jobs 
CROSS JOIN (
    SELECT count(*) totalJobs FROM jobs 
    WHERE (
        ( JSON_CONTAINS(skills, '{"id":1,"val":"Carpenter"}') ) 
        AND  NOT JSON_CONTAINS(workers, '{"id":6,"fullname":"Cip"}') 
        AND NOT JSON_CONTAINS(applicants, '{"id":6,"fullname":"Cip"}')
    )
) ttl
CROSS JOIN (
    SELECT AVG(
        (SELECT AVG(professional) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(communication) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(safety) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(respectful) FROM businesses_ratings WHERE business_id=jobs.business_id) + 
        (SELECT AVG(dependability) FROM businesses_ratings WHERE business_id=jobs.business_id)
    ) business_rating FROM businesses_ratings WHERE business_id=jobs.business_id
) avg
WHERE (
    ( JSON_CONTAINS(skills, '{"id":1,"val":"Carpenter"}') ) 
    AND  NOT JSON_CONTAINS(workers, '{"id":6,"fullname":"Cip"}') 
    AND NOT JSON_CONTAINS(applicants, '{"id":6,"fullname":"Cip"}')
) 
ORDER BY start_date LIMIT 3

and the error:

Unknown column 'jobs.business_id' in 'where clause'

Average is sum/count: `(AVG(professional)+AVG(communication)+AVG(safety)+AVG(respectful)+AVG(dependability)/5`. Or `sum(professional+communication+safety+respectful+dependability)/5*count(*)` — dnoeth, Sep 03 '20 at 13:30

GMB · Answer 1 · 2020-09-03T13:55:55.723

1

I think you want aggregation and window functions:

select 
    business_id, 
    rank() over(
        order by avg(professional) + avg(communication) + avg(safety)
    ) as rn
from businesses_ratings
group by business_id

You can expand the order by clause of the window function with additional columns as needed.

I am quite skeptical about the interest of ranking by the average of the 3 averages - but the above query seems like a reasonable interpretation of what you ask for.

In earlier versions, one option uses user-defined variables to compute the rank:

select t.*, @rn := @rn + 1 rn
from (
    select 
        business_id, 
        avg(professional) + avg(communication) + avg(safety) sum_avg
    from businesses_ratings
    group by business_id
    order by sum_avg
) t
cross join (select @rn := 0) x

edited Sep 03 '20 at 13:55

answered Sep 03 '20 at 13:18

GMB

216,147
25
84
135

Thanks @gmb, but I need to select * from jobs and append the overall rating of the business_id to each result. Running your query, returns this error: `Error in query (1064): Syntax error near '( order by avg(professional) + avg(communication) + avg(safety) ) ' at line 3` – xyboox Sep 03 '20 at 13:32
@xyboox: which version of MySQL are you running? Window functions are available in MySQL 8.0 only. – GMB Sep 03 '20 at 13:34
running MySQL v5.7.30 – xyboox Sep 03 '20 at 13:37
Thank you GMB, your solution works as well with some very small adjustments. I accepted @gordon-linoff answer only because it came up before yours and also worked "out of the box". – xyboox Sep 03 '20 at 14:17

score 0 · Accepted Answer · answered Sep 03 '20 at 13:52

0

Your query seems way more complicated than necessary. I think you want:

select br.business_id,
       avg(professional),
       avg(communication),
       avg(safety),
       avg(respectful),
       avg(dependability),
       (avg(professional) + avg(communication) + avg(safety) + avg(respectful) + avg(dependability)) / 5 as overall_avg
from businesses_ratings br
group by br.business_id;

answered Sep 03 '20 at 13:52

Gordon Linoff

1,242,037
58
646
786

Thanks, inserted your select in my CROSS JOIN and works just the way I need it. – xyboox Sep 03 '20 at 14:18

MySQL ( v5.7.30 ) query AVG of AVGs

2 Answers2