Sub-Select with MAX / SUM / INNER JOIN?

1

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

I was able to do 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 description of the product, only the product that had the highest billing. 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 it returns all the records and I only need the one that had the highest billing.

    
asked by anonymous 01.05.2017 / 17:01

1 answer

0

Thank you, Motta! I was able to use the tools you gave me. It looked something 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;'
    
02.05.2017 / 19:36