MYSQL question order the salesman with their average commission rate-CodePudding

MariaDB [test]> select * from Salesman;

| empId | name            | gender |
|     3 | Suresh Raina    | M      |
|     4 | Surech Raina    | M      |
|     5 | Surech Raina    | F      |
|    11 | Andrew Flintoff | M      |
|    18 | Moeen Ali       | M      |
|    45 | Rohit Sharma    | M      |

MariaDB [test]> select * from Purchases;

| clientId | empId | packageId | commissionRate |
|        7 |    18 |       257 |          10.50 |
|       18 |    11 |       183 |           4.50 |
|       19 |     3 |       301 |           9.00 |
|       55 |    45 |       110 |           8.00 |

These are the two tables I need help querying I need to display the salesman name and the avg commission rate in desc order of avg commission rate. This is what I have so far but I think this is incorrect.

select name , avg(commissionRate)
  from Salesman
natural JOIN Purchases
 order by avg(commissionRate) desc;

CodePudding user response：

It can be done with group by after joining tables :

select s.name , avg(p.commissionRate) from 
 Purchases p inner JOIN Salesman s  on s.empId=p.empId 
group by s.name
order by 2 desc