1

I have written a query which output the sale figures of all employees, and their name and what shop they work at. But I am trying to find the top sales person for each shop. Can you please explain how can i only show the top sales person for each shop. I was thinking that i can use

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

Here is screenshot of my Results so far

Seb
  • 1,521
  • 1
  • 12
  • 19
J.Dave
  • 67
  • 2
  • 6
  • There's no DISTINCT function in SQL. – jarlh Dec 09 '16 at 15:02
  • Possible duplicate of [Given two tables, select all data from one table and only the most recent from the other](http://stackoverflow.com/questions/40979927/given-two-tables-select-all-data-from-one-table-and-only-the-most-recent-from-t) – CLAbeel Dec 09 '16 at 15:20
  • 1
    This question may be a duplicate, but given the complexity of the query I doubt the OP would get the answet right on his own. Add to the complexity that MySQL does not have cte or row number. – Tim Biegeleisen Dec 09 '16 at 15:25
  • i think we can make this query a bit more efficient could you post the columns in the Employee Table, Shop Table and Film Rental and i should then be able to help. – Luke Franklin Dec 09 '16 at 15:38

2 Answers2

0

Here is a really ugly way:

SELECT t1.empname,
       t1.shopname,
       t2.maxsales
FROM
(
    SELECT empname, shopname, SUM(rentalrate) AS sales
    FROM frs_FilmRental
    NATURAL JOIN frs_Employee
    NATURAL JOIN frs_Shop
    GROUP BY empname, shopname
) t1
INNER JOIN
(
    SELECT t.shopname, MAX(t.Sales) AS maxsales
    FROM
    (
        SELECT shopname, SUM(rentalrate) AS Sales
        FROM frs_FilmRental
        NATURAL JOIN frs_Employee
        NATURAL JOIN frs_Shop
        GROUP BY empname, shopname
    ) t
    GROUP BY t.shopname
) t2
    ON t1.shopname = t2.shopname AND
       t1.sales    = t2.maxsales

You will recognize the subquery t1 as simply being your original query. The t2 subquery restricts this result set to only those shops which had the highest sales, implying only the record corresponding to the employeee with the highest sales. In the event of a tie, both records would be returned for that shop.

The query is a bit verbose, but this owes in part to that MySQL does not have support for row number or common table expressions.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0
   SELECT *
    FROM
    (
      SELECT DISTINCT empname, shopname,
      SUM( rentalrate ) OVER (PARTITION BY shopname,empname ) AS Sales,
      ROW_NUMBER OVER (PARTITION BY shopname,empname ) AS RN
      FROM frs_FilmRental
      NATURAL JOIN frs_Employee
      NATURAL JOIN frs_Shop
      GROUP BY empnin, shopname 
                                 ) X
    WHERE X.RN=1
Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38