Add cases to a query

Question

I need to create a query that adds up to 2 cases, but I’m not getting it the way I did. Case the field D3_UM = 'PC', he must add the field D3_QTSEGUM. Case the field D3_UM <> 'PC', he must add the field D3_QUANT.

In the end, he must add up the fields D3_QTSEGUM and D3_QUANT and bring the result.

Bring the sum of each, got, but it lists 2 records (one for when the field is equal to PC and another for when the field is different from PC). Could give me a light?

Follows script from this to cited. It is a subselect

    SELECT...,
    (SELECT CASE 
                WHEN sd3_sub1.D3_UM = 'PC' THEN sum(sd3_sub1.D3_QTSEGUM) 
                WHEN sd3_sub1.D3_UM <> 'PC' THEN sum(sd3_sub1.D3_QUANT)
         END AS D3_QUANT
                FROM SD3010 AS sd3_sub1 WITH(NOLOCK)        
                WHERE sd3_sub1.D3_TM = '010'                
                    AND sd3_sub1.D3_LOCAL IN ('01','02','98')
                    AND sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01')
                                                    AND (LEFT(sd3.D3_EMISSAO,6) + '31')             
                    AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                    AND sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'             
                    AND sd3_sub1.D_E_L_E_T_ <> '*' group by sd3_sub1.D3_UM) AS Producao
FROM ...

With the WITH worked (follows below), however, for being a subselect is giving error in query, when implement in original query (which has the subselects, as quoted above).

WITH SOMATORIA (SOMA_TOTAL) AS
(SELECT sum(CASE 
            WHEN sd3_sub1.D3_UM = 'PC' THEN sd3_sub1.D3_QTSEGUM 
            WHEN sd3_sub1.D3_UM <> 'PC' THEN sd3_sub1.D3_QUANT
     END) AS D3_QUANT
            FROM SD3010 AS sd3_sub1 WITH(NOLOCK)        
            WHERE sd3_sub1.D3_TM = '010'                
                AND sd3_sub1.D3_LOCAL IN ('01','02','98')
                AND sd3_sub1.D3_EMISSAO BETWEEN ('20170201')
                                                AND ('20170231')                
                --AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
                AND sd3_sub1.D3_CC LIKE ('13603%')              
                AND sd3_sub1.D_E_L_E_T_ <> '*'
                group by D3_UM
                )
                SELECT SUM(SOMA_TOTAL) FROM SOMATORIA

Answer 1

You can use the else in his case:

WITH SOMATORIA (SOMA_TOTAL) AS
  (SELECT CASE WHEN 
        sd3_sub1.D3_UM = 'PC' THEN sum(sd3_sub1.D3_QTSEGUM) 
        ELSE sum(sd3_sub1.D3_QUANT)
   END AS D3_QUANT
        FROM SD3010 AS sd3_sub1 WITH(NOLOCK)        
        WHERE sd3_sub1.D3_TM = '010'                
            AND sd3_sub1.D3_LOCAL IN ('01','02','98')
            AND sd3_sub1.D3_EMISSAO BETWEEN (LEFT(sd3.D3_EMISSAO,6) + '01')
                                            AND (LEFT(sd3.D3_EMISSAO,6) + '31')             
            AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
            AND sd3_sub1.D3_CC LIKE LEFT(sd3.D3_CC,5) + '%'             
            AND sd3_sub1.D_E_L_E_T_ <> '*' group by sd3_sub1.D3_UM)
SELECT SUM(SOMA_TOTAL) FROM SOMATORIA

It will be necessary to use the WITH because the SUM would not allow another aggregation or sub-consumption function.

See more in Using WITH AS command in Sql Server.