'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'

sqlmysqlaggregate-functions


Solution 1:[1]

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"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions