Mysql - How To Filter And Show Max Value From A Group?

I have created the following query that will give me sales figures from all the stores in my database: SELECT s.shopname AS 'Store', e.empname AS 'Employee', e1.empna

Solution 1:

One possible solution is to use HAVING and ALL

SELECT s.shopname AS"Store",
   e.empname AS"Employee",
   e1.empname AS"Manager",
   SUM(p.amount) AS"Total Sales"FROM fss_Shop s
   JOIN fss_Employee e ON e.shopid = s.shopid
   JOIN fss_Payment p ON p.empnin = e.empnin
   JOIN fss_Employee e1 ON e1.empnin = e.mgrnin
GROUPBY e.empname, s.shopid
HAVING SUM(p.amount) >= ALL(
   SELECT SUM(p.amount)
   FROM fss_Employee e
   JOIN fss_Payment p ON p.empnin = e.empnin
   WHERE e.shopid = s.shopid
   GROUPBY e.empname
)

if ALL doesn't work for you. You may change it like this

SELECT s.shopname AS"Store",
   e.empname AS"Employee",
   e1.empname AS"Manager",
   SUM(p.amount) AS"Total Sales"FROM fss_Shop s
   JOIN fss_Employee e ON e.shopid = s.shopid
   JOIN fss_Payment p ON p.empnin = e.empnin
   JOIN fss_Employee e1 ON e1.empnin = e.mgrnin
GROUPBY e.empname, e.shopid
HAVING SUM(p.amount) = (
   SELECT MAX(t.samount)
   FROM
   (
     SELECT SUM(p.amount) samount
     FROM fss_Employee emp
     JOIN fss_Payment p ON p.empnin = emp.empnin
     WHERE emp.shopid = e.shopid
     GROUPBY emp.empname
   ) t
)

ok and the next version that avoids correlated subquery behind FROM

SELECT s.shopname AS"Store",
   e.empname AS"Employee",
   e1.empname AS"Manager",
   SUM(p.amount) AS"Total Sales"FROM fss_Shop s
   JOIN fss_Employee e ON e.shopid = s.shopid
   JOIN fss_Payment p ON p.empnin = e.empnin
   JOIN fss_Employee e1 ON e1.empnin = e.mgrnin
GROUPBY e.empnin, e.shopid
HAVING (e.shopid, SUM(p.amount)) IN
(
  SELECT t.shopid, MAX(t.samount)
  FROM 
  (
     SELECT emp.shopid, SUM(p.amount) samount
     FROM fss_Employee emp
     JOIN fss_Payment p ON p.empnin = emp.empnin
     GROUPBY emp.empname, emp.shopid
  ) t
  GROUPBY t.shopid
)