Sub-Select with MAX / SUM / INNER JOIN?

Question

I need to make a SUM within a MAX to return higher product billing within a summary.

I got by doing this:

SELECT MAX(Faturamento) MAX_Faturamento 
FROM (SELECT SUM(P.preco * I.qtde) Faturamento 
    FROM produto P 
    INNER JOIN itens I ON P.cod_prod = I.cod_prod 
    GROUP BY P.descricao) AS tabela_aux;

But I also need to bring the product description, only the product that had the highest turnover. I tried using this:

SELECT Descricao, MAX(Faturamento) MAX_Faturamento 
FROM (SELECT P.descricao, SUM(P.preco * I.qtde) Faturamento 
    FROM produto P INNER JOIN itens I ON P.cod_prod = I.cod_prod 
    GROUP BY P.descricao) AS tabela_aux 
GROUP BY Descricao;`

But he returns all the records and I only need what had the highest turnover.

Answer 1

Thanks, Motta! I got it using the tools you gave me. It looks like this:

'SELECT Descricao, MAX(Faturamento) Faturamento FROM (SELECT SUM(P.preco * I.qtde) Faturamento, P.descricao FROM produto P INNER JOIN itens I ON P.cod_prod = I.cod_prod GROUP BY P.descricao) AS tab_aux GROUP BY Descricao ORDER BY Faturamento DESC LIMIT 1;'