How to use max function in SQL

Question

I am trying to write a query to find the top sales person in the shop and trying to Display the employee name, the shop where he/she is employed and the value of sales. I only want to output the employee with highest number of sales at the moment, my query outputs all the employees with their figures.

SELECT empnin, shopname, SUM( Rentalrate ) AS Sales
FROM frs_FilmRental
NATURAL JOIN frs_Shop
GROUP BY empnin

This is the following results I get with my query:

Answer 1

If you want the top sales person per shop, then you need to filter. This is best done in the WHERE clause.

In MySQL, this type of query is actually most easily accomplished using variables:

select fs.*
from (select fs.*,
             (@rn := if(@s = shopname, @rn + 1,
                        if(@s := shopname, 1, 1)
                       )
             ) as rn
      from (select fr.empnin, s.shopname, SUM(fr.Rentalrate) AS Sales
            from frs_FilmRental fr join
                 frs_Shop s
                 using (??)  -- add the appropriate column here
            group by empnin
           ) fs cross join
           (select @rn := 0, @s := '') params
      order by shopname, sales desc
     ) fs
where rn = 1;

Also, don't use natural join. It is a bug waiting to happen, because it hides the keys used for joining and some unexpected columns might end up being used.