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:

Results

score 0 · Answer 1 · answered Dec 05 '16 at 20:27

0

Here, like this:

SELECT top 10 empnin, min(shopname) as Shopname, SUM( Rentalrate ) AS Sales
FROM frs_FilmRental
NATURAL JOIN frs_Shop
GROUP BY empnin
order by sum(Rentalrate) desc

answered Dec 05 '16 at 20:27

Anand

1,165
10
18

score 0 · Answer 2 · edited Dec 05 '16 at 20:32

0

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

Try the above for sql server

edited Dec 05 '16 at 20:32

SQLChao

7,709
1
17
32

answered Dec 05 '16 at 20:31

Swetha

457
4
7

score 0 · Accepted Answer · answered Dec 05 '16 at 21:01

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.

How to use max function in SQL

3 Answers3