MySQL averaging joined table

debugcn Published at Dev

prgDevelop

I have two tables:

salon_ranks:

+-----------+-----------+-----------+-----------+
| salon_id  |category_id|  user_id  |    rank   |
+-----------+-----------+-----------+-----------+
|        10 |         1 |       999 |       1   |
|        10 |         2 |       999 |       1   |
|        10 |         1 |       888 |       5   |
|        10 |         2 |       888 |       5   |
+-----------+-----------+-----------+-----------+

categories:

+------+-----------+
|  id  |    name   |
+------+-----------+
|   1  |   prices  |
|   2  |   service |
+------+-----------+

I'm trying to efficiently get something that looks like this:

+-----------+-----------+-----------+
| salon_id  |category_id| AVG(rank) |
+-----------+-----------+-----------+
|        10 |         1 |       3   |
|        10 |         2 |       3   |
+-----------+-----------+-----------+

What made most sense to me is a query like that:

SELECT salon_id, category_id, AVG(rank)
FROM salon_ranks
INNER JOIN categories ON category_id = id
GROUP BY salon_id, category_id

But for some reason I don't get the average, but get this instead:

+-----------+-----------+-----------+
| salon_id  |category_id| AVG(rank) |
+-----------+-----------+-----------+
|        10 |         1 |       1   |
|        10 |         2 |       5   |
+-----------+-----------+-----------+

Any idea for why it would happen? And what might be the correct query?

I'm sorry, it was my mistake. I got the data from my original tables wrong. Really sorry for misleading you :(

Robert

Now we know your query works fine. But it's good practice to use aliases for tables. It's not necessary for your case but it makes your query more readable and scalable.

SELECT SR.salon_id, SR.category_id, AVG(SR.rank)
FROM salon_ranks SR
INNER JOIN categories C ON SR.category_id = C.id
GROUP BY SR.salon_id, SR.category_id

Collected from the Internet

Please contact [email protected] to delete if infringement.