MySQL - How to join two subqueries

Question

I am trying to create a query that will give me the: - storename - top salesperson - salesperson sales value - manager name

I have managed to create the two following queries. Both these queries work but I just need help in joining them together.

Get top salesperson from each store.

SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS sales 
FROM fss_Payment GROUP BY empnin) AS salesdata GROUP BY shopid

Get All Managers and their store

SELECT s.shopname, e.empname FROM fss_Shop s 
JOIN fss_Employee e ON e.shopid = s.shopid AND e.mgrnin="" 
ORDER BY s.shopid

Now I need to join the results of the two queries. I tried to do it like below:

SELECT * FROM
(SELECT s.shopname, e.empname FROM fss_Shop s 
JOIN fss_Employee e ON e.shopid = s.shopid AND e.mgrnin="" 
) x 
JOIN
(SELECT MAX(sales) FROM (SELECT shopid, SUM(amount) AS sales 
    FROM fss_Payment GROUP BY empnin) AS salesdata GROUP BY shopid
) y 
ON x.shopid = y.shopid

See what some of my tables look like at this link - https://www.db-fiddle.com/f/t94XmTEMgXpmSLS3e8HWAh/1

Answer 1

UPDATE

MySQL 8.0 introduces Window Functions

https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

The original answer below is still applicable to versions of MySQL before 8.0.

---

ORIGINAL ANSWER

We would think this would be a simple thing to do. And some databases give us analytic/windowing functions which makes this fairly easy. In MySQL, we have a couple of options: go with a straight SQL approach, or make use of some user-defined variables to emulate analytic functions.

For the straight SQL approach, we can build the query a step at a time.

First, we can get total sales for each salesperson from each store

  SELECT p.shopid
       , p.empnin
       , SUM(p.amount) AS sales
    FROM fss_Payment p
   GROUP
      BY p.shopid
       , p.empnin
   ORDER
      BY p.shopid ASC
       , SUM(p.amount) DESC

Review the results and verify that this is correct. And then we can use that query as an inline view in another query, to get the "highest" total sales for each store:

  -- get highest total sales for each store
  SELECT q.shopid
       , MAX(q.sales) AS highest_sales
    FROM ( SELECT p.shopid
                , p.empnin
                , SUM(p.amount) AS sales
             FROM fss_payment p
            GROUP
               BY p.shopid
                , p.empnin
         ) q
   GROUP
      BY q.shopid
   ORDER
      BY q.shopid

We can take that result, and join it back to the total sales for each salesperson/store, to get the salesperson that had a matching "highest" sales for that store

  -- get salesperson with the highest total sales for each store
  SELECT t.shopid
       , t.empnin
       , t.sales
    FROM ( SELECT q.shopid
                , MAX(q.sales) AS highest_sales
             FROM ( SELECT p.shopid
                         , p.empnin
                         , SUM(p.amount) AS sales
                      FROM fss_payment p
                     GROUP
                        BY p.shopid
                         , p.empnin
                  ) q
            GROUP
               BY q.shopid
         ) r
    JOIN ( SELECT s.shopid
                , s.empnin
                , SUM(s.amount) AS sales
             FROM fss_payment s
            GROUP
               BY s.shopid
                , s.empnin
         ) t
      ON t.shopid = r.shopid
     AND t.sales  = r.highest_sales

If there are two (or more) empnin with the same highest total sales (a tie for first place), this query returns both (or all) of those salesperson.

Now we just need to add a join to fss_shop to get the storename, and a couple of joins to fss_employee to get the salesperson name, and to get that saleperson's manager

add this to the query,

   JOIN fss_shop h 
     ON h.shopid = t.shopid
   LEFT
   JOIN fss_employee e
     ON e.empnin = t.empnin 
   LEFT
   JOIN fss_employee m
     ON m.empnin = e.mgrnin

add an order by clause

  ORDER BY h.storename, e.empname

and add the appropriate expressions to the SELECT list.

Putting that all together, we get something like this:

  SELECT h.shopname       AS `storename` 
       , e.empname        AS `top salesperson`
       , t.sales          AS `salesperson sales value`
       , m.empname        AS `manager name`
    FROM ( SELECT q.shopid
                , MAX(q.sales) AS highest_sales
             FROM ( SELECT p.shopid
                         , p.empnin
                         , SUM(p.amount) AS sales
                      FROM fss_payment p
                     GROUP
                        BY p.shopid
                         , p.empnin
                  ) q
            GROUP
               BY q.shopid
         ) r
    JOIN ( SELECT s.shopid
                , s.empnin
                , SUM(s.amount) AS sales
             FROM fss_payment s
            GROUP
               BY s.shopid
                , s.empnin
         ) t
      ON t.shopid = r.shopid
     AND t.sales  = r.highest_sales
    JOIN fss_shop h 
      ON h.shopid = t.shopid
    LEFT
    JOIN fss_employee e
      ON e.empnin = t.empnin 
    LEFT
    JOIN fss_employee m
      ON m.empnin = e.mgrnin
   ORDER BY h.storename, e.empname

---

To answer the question you asked, how to join those two queries together to get the specified result: I don't think it's possible out of those two queries.

---

The manager name being returned is the manager of the employee. If we want to get the manager of the shop, change the join criteria for m to match to h instead of e.

The other approach I mentioned is to make use of user-defined variables. With that approach, it's easier to get just one "top salesperson" per store (when the specification is that there will always be a "tie breaker". There aren't any ties for top salesperson.)

Using user-defined variables, we might get better performance with large sets. But that approach also relies on behavior that is not guaranteed, as documented in the MySQL Reference Manual.