MySQL Subquery Busting Error

Navigation
1

I have a select to display 4 fields, but a field that is valor_pago is from another table, but I can not use JOIN otherwise the return of records conflicts

So I created a Subquery but my question is: how do I get my amount of SUM(valor_pago) to be grouped by month. I tried to change my subquery in two ways, without success:

SELECT MONTH(data_pagamento, SUM(valor_pago)

  

Operand should countaim 1 columns

And I tried to group with GROUP BY MONTH(data_pagamento)

  

Subquery returns more than 1 row

The error is that it does the sum sum and all fields with this code I'm using 

    SELECT 
        MONTH(P.data_venda), 
        SUM(P.vlr_tot_liquido) AS Faturamento, 
        SUM(P.custo_total) AS Custos, 
        SUM(P.vlr_tot_liquido) - SUM(P.custo_total) AS Lucro, 
       (SELECT 
            SUM(valor_pago) 
        FROM contapag
        WHERE empresa = 1 
        AND filial = 1 
        AND data_pagamento BETWEEN '2016-01-01' AND '2016-12-31' 
        AND valor_pago > 0) AS Despesas 
    FROM pedido P
    LEFT JOIN (SELECT Min(pedido) as formapag_rec,empresa, filial, pedido, plano as formapag_plano FROM formapag
       WHERE empresa = 1  AND filial = 1
       GROUP BY empresa, filial, pedido) F ON F.empresa = P.empresa and F.filial = P.filial and F.pedido = P.pedido
   LEFT JOIN plano on plano.plano = formapag_plano
       WHERE P.empresa = 1 AND P.filial = 1 AND data_venda BETWEEN '2016-01-01' AND '2016-12-31' AND P.vlr_tot_liquido > 0 AND P.status = 'FIN' AND (plano.especie <> 'MOV' OR plano.especie is null)
       GROUP BY YEAR(P.data_venda), MONTH(P.data_venda)

    
asked by anonymous 28.11.2017 / 19:42

1 answer

2

Only filter the subquery by the month of the main query: 

        SELECT 
            MONTH(P.data_venda), 
            SUM(P.vlr_tot_liquido) AS Faturamento, 
            SUM(P.custo_total) AS Custos, 
            SUM(P.vlr_tot_liquido) - SUM(P.custo_total) AS Lucro, 
           (SELECT 
                SUM(valor_pago) 
            FROM contapag
            WHERE empresa = 1 
            AND filial = 1 
            AND data_pagamento BETWEEN '2016-01-01' AND '2016-12-31' 
            AND valor_pago > 0 
            AND MONTH(data_pagamento) = MONTH(P.data_venda)
            AND YEAR(data_pagamento) = YEAR(P.data_venda)) AS Despesas 
        FROM pedido P
        LEFT JOIN (SELECT Min(pedido) as formapag_rec,empresa, filial, pedido, plano as formapag_plano FROM formapag
           WHERE empresa = 1  AND filial = 1
           GROUP BY empresa, filial, pedido) F ON F.empresa = P.empresa and F.filial = P.filial and F.pedido = P.pedido
       LEFT JOIN plano on plano.plano = formapag_plano
           WHERE P.empresa = 1 AND P.filial = 1 AND data_venda BETWEEN '2016-01-01' AND '2016-12-31' AND P.vlr_tot_liquido > 0 AND P.status = 'FIN' AND (plano.especie <> 'MOV' OR plano.especie is null)
           GROUP BY YEAR(P.data_venda), MONTH(P.data_venda)
    
28.11.2017 / 19:53
Use of the "Instance of" Update database modal