MySQL Query - SELECT (average of a category) AS "CATEGORY AVERAGE"

Objective: When user browses to a particular seller, then display his average along with the average of sellers from similar category for easy comparison.

Example Data:

Seller | Category |  Qty |  Sales  
--------------------------------------------  
Harry  | Mango    | 100  |  50000  
John   | Apple    | 75   |  50500  
Max    | Mango    | 44   |  20000  
Ash    | Mango    | 60   |  35000  
Lingo  | Apple    | 88   |  60000  

Required Output: (When user browses to Ash)

Quantity Sold by Ash: 60

Average quantity sold by other Mango sellers: 68 (Avg of 100, 44 & 60)

Average Price of Ash: 583.33 (35000 / 60)

Average Price of Other Mango Sellers: 514.70 (Weighted Average of Prices)

Skeleton Code:

SELECT 'Qty' AS 'Qty',

(SOME CODE) AS 'Avg Qty',

('Sales' / 'Qty'开发者_如何学运维) AS 'Price',

(SOME CODE) AS 'Avg Price'

FROM 'SALES TABLE'

WHERE 'Seller' = 'Ash'

Use:

SELECT yt.qty,
       x.cat_avg,
       yt.sales/yt.qty AS avg_price,
       NULL AS weighted_average 
  FROM YOUR_TABLE yt
  JOIN (SELECT t.category,
               AVG(t.qty) AS cat_avg
          FROM YOUR_TABLE t
      GROUP BY t.category) x ON x.category = yt.category
 WHERE yt.seller = 'Ash'

I'll fill in the weighted average when you supply the equation, if possible.

SELECT
    sellers.Qty,
    ROUND(AVG(s.Qty), 0) AS TotalQty,
    ROUND(( sellers.Sales / sellers.Qty ), 2) AS AvgPrice,
    ROUND(( AVG(s.Sales) / AVG(s.Qty) ),2) AS TotalAvgPrice
FROM
    sellers 
LEFT JOIN sellers AS s On sellers.Category = s.Category
WHERE
    sellers.Seller = "Ash"