MySQL Query - MAX() not working on the columns-CodePudding

I have two attributes productType and Profits. It is currently, returning all the rows with productType and its profits. I only want the row(s) with max profit. So, I used MAX() around the second attribute, which calculates the profits from other tables in my database. However, the output is still printing all rows, the MAX() doesn't seem to be working.

SELECT 
    outerP.prodType AS PType,
    MAX((SELECT 
            SUM(ProductPrice.Price)
        FROM
            ProductPrice
        WHERE
            ProductPrice.ProdType = outerP.prodType) - (SELECT 
            SUM(TotalPartCost)
        FROM
            PartsPurchase
        WHERE
            PartsPurchase.ProductID IN (SELECT 
                    ProductID
                FROM
                    Product
                WHERE
                    Product.prodType = outerP.prodType))) AS Profit
FROM
    Product AS outerP
        INNER JOIN
    ProductPrice ON (outerP.prodType = ProductPrice.ProdType
        AND outerP.Style = ProductPrice.Style)
GROUP BY outerP.prodType

Current Output: 'Phone','120.92999935150146' 'Tablet','159.89999675750732'

Expected Output: 'Tablet','159.89999675750732'

I tried putting MAX() around the second attribute. I even tried wrapping a new SELECT statement around the above query to find the MAX() but it is not working. It is outputting all the rows no matter what.

CodePudding user response：

you can use ORDER BY profit and LIMIT to get the wanted result

SELECT 
    outerP.prodType AS PType,
    MAX((SELECT 
            SUM(ProductPrice.Price)
        FROM
            ProductPrice
        WHERE
            ProductPrice.ProdType = outerP.prodType) - (SELECT 
            SUM(TotalPartCost)
        FROM
            PartsPurchase
        WHERE
            PartsPurchase.ProductID IN (SELECT 
                    ProductID
                FROM
                    Product
                WHERE
                    Product.prodType = outerP.prodType))) AS Profit
FROM
    Product AS outerP
        INNER JOIN
    ProductPrice ON (outerP.prodType = ProductPrice.ProdType
        AND outerP.Style = ProductPrice.Style)
GROUP BY outerP.prodType
order BY Profit DESC
LIMIT 1