CODEWITHSUNDEEP
mysqlsql
Lucas B.
Lucas B.

Reputation: 509

Duplicate sum operation

The sum operation is called more than once in my query and so the result that appears in column B2_ESTOQUE becomes inconsistent, maybe the cause of this is the amount of records that return in select and left join operations. That is an assumption.

Is there an error in the query below? Is the left join operation being used incorrectly?

    SELECT
    SB1.B1_FILIAL, SB1.B1_COD, SB1.B1_DESC, SB1.B1_TIPO, SB1.B1_UM, SB1.B1_SEGUM, SB1.B1_GRUPO, SB1.B1_CONV, SB1.B1_TIPCONV, SB1.B1_PRV1,
    SB1.B1_PESO, SB1.B1_CODBAR, SB1.B1_QE, SB1.B1_MSBLQL, SB1.D_E_L_E_T_, SB1.R_E_C_N_O_, SB1.MD5, SB1.DATA_UPDATE,
        (SELECT BM_DESC FROM PADRAO.PORTAL_SBM001 WHERE BM_GRUPO = SB1.B1_GRUPO   LIMIT 0,1) as B1_GRUPO_DESCRICAO,
    (SUM(IFNULL(SB2.B2_QATU, 0)) - SUM(IFNULL(SB2.B2_RESERVA, 0)) - SUM(IFNULL(SB2.B2_QPEDVEN, 0))) - SUM(IFNULL(SC6.C6_QTDVEN, 0)) AS B2_ESTOQUE
    FROM PADRAO.PORTAL_SB1001 SB1
    LEFT JOIN PADRAO.PORTAL_SB2001 SB2
    ON SB1.B1_COD = SB2.B2_COD
    LEFT JOIN PADRAO.PORTAL_SC6001_NEW SC6
    ON SB1.B1_COD = SC6.C6_PRODUTO
AND (SC6.C6_NUM IS NULL OR SC6.C6_NUM = '')
    AND SB2.B2_LOCAL IN ('07','08')
    WHERE (SB1.D_E_L_E_T_ IS NULL OR SB1.D_E_L_E_T_ = '')
    AND (SB2.D_E_L_E_T_ IS NULL OR SB2.D_E_L_E_T_ = '')
    AND (SC6.D_E_L_E_T_ IS NULL OR SC6.D_E_L_E_T_ = '')
    AND (SB1.B1_MSBLQL IS NULL OR SB1.B1_MSBLQL <> '1')
    GROUP BY SB1.B1_COD;

Upvotes: 1

Views: 59

Answers (1)

xQbert
xQbert

Reputation: 35323

I wasn't sure what all the checks for isnull and '' were about in the where clauses. I used coalesce to change null's to empty string and eliminated the values in the subqueries where clause instead of in the outermost where clause; but perhaps you wanted to keep the left joins Nulls which is why you checked for emptying string and null...

Nevertheless here's what I have as a UNTESTED query. Notice I converted the two left joined tables to sum the values before the join and group by the joined keys. 

  SELECT
    SB1.B1_FILIAL
  , SB1.B1_COD
  , SB1.B1_DESC
  , SB1.B1_TIPO
  , SB1.B1_UM
  , SB1.B1_SEGUM
  , SB1.B1_GRUPO
  , SB1.B1_CONV
  , SB1.B1_TIPCONV
  , SB1.B1_PRV1
  , SB1.B1_PESO
  , SB1.B1_CODBAR
  , SB1.B1_QE
  , SB1.B1_MSBLQL
  , SB1.D_E_L_E_T_
  , SB1.R_E_C_N_O_
  , SB1.MD5
  , SB1.DATA_UPDATE
  ,(SELECT BM_DESC FROM PADRAO.PORTAL_SBM001 WHERE BM_GRUPO = SB1.B1_GRUPO   LIMIT 0,1) as B1_GRUPO_DESCRICAO
  , SB2.mSum -  SB6.mSUM as B2_ESTOQUE

    FROM PADRAO.PORTAL_SB1001 SB1

    LEFT JOIN (SELECT SB2.B2_COD, SUM(IFNULL(B2_QATU, 0) - IFNULL(B2_RESERVA, 0) - IFNULL(B2_QPEDVEN, 0)), AS mSum
               FROM PADRAO.PORTAL_SB2001
               WHERE B2_LOCAL IN ('07','08')
                 AND coalesce(SB2.D_E_L_E_T_,'')=''
               GROUP BY SB2.B2_COD) SB2
      ON SB1.B1_COD = SB2.B2_COD


    LEFT JOIN (SELECT SUM(IFNULL(SC6.C6_QTDVEN, 0)) mSUM, C6_PRODUTO 
               FROM PADRAO.PORTAL_SC6001_NEW SC6
               WHERE coalesce(SC6.C6_NUM,'') = ''
                 AND coalesce(SC6.D_E_L_E_T_,'') = ''
               GROUP BY C6_PRODUTO)
      ON SB1.B1_COD = SC6.C6_PRODUTO

    WHERE (SB1.D_E_L_E_T_ IS NULL OR SB1.D_E_L_E_T_ = '')
      AND (SB1.B1_MSBLQL IS NULL OR SB1.B1_MSBLQL <> '1')

    GROUP BY SB1.B1_COD;

Upvotes: 2

Related Questions