Respecting GROUP BY Clause in a MySQL SUB-QUERY

I have a query which returns some figures from a table of sales data. The table contains data for a number of different sales managers. I need to return data for the individual managers and also some calculated figures. One of the figures I’m trying to get at involves a subquery. Getting the figures for each individual manager is fine and works well. The problem occurs when I am trying to get a figure which involves the use of a subquery. It seems that, though the outer query uses a group by clause to separate out individual salespeople, the subquery operates on the entire set.

Sample Data

name | Amount | Sell_at | Profit
--------------------------------
Fred | 1      | 3.99    | 0.99
Joe  | 2      | 10.50   | 5.00
Fred | 5      | 20.00   | 15.00
Joe  | 10     | 10.00   | 6.00

Desired result:

name | Total Profit | < 50% | > 50%
------------------------------------
Fred |    75.99     | 0.99  | 75.00
Joe  |    71.00     | 60    | 10

SELECT 
Account_Manager,
SUM(Profit * Amount) AS 'Total Profit'
(SELECT sum(Profit * Amount) from sales WHERE Profit * Amount / (Sell_at * Amount) < 0.5) AS '< 50%',
(SELECT sum(Profit * Amount) from sales WHERE Profit * Amount / (Sell_at * Amount) > 0.5) AS '> 50%'
FROM sales WHERE Invoice_Date = 'some date' GROUP BY Account_Manager

This gives me a row for each salesperson and their profit for that day, but the sub queries return figures totaled from the entire table. I could add a clause to the subquery WHERE in order to limit the result to the same date as the outer query, but ideally what I need to do really is to get the results for each individual salesperson.

Am I on the right track or is there another way I should be approaching this?

Answer

If you want to classify the rows based on their percentile, then use window functions. Let me assume that you want to know who is above and below average:

SELECT Account_Manager,
       SUM(Profit * Amount) AS Total_Profit,
       (CASE WHEN SUM(Profit * Amount) > AVG(SUM(Profit * Amount)) OVER ()
             THEN 'Above average'
             WHEN SUM(Profit * Amount) < AVG(SUM(Profit * Amount)) OVER ()
             THEN 'Below average'
             ELSE 'Average'
        END) as relative_position
FROM sales
WHERE Invoice_Date = 'some date'
GROUP BY Account_Manager;