CODEWITHSUNDEEP
sqlgroup-bysum
Flusko
Flusko

Reputation: 69

Sum duplicate row values from two tables

I'm trying to get the following information from two tables in one row, but when i use group by i always get two rows:

Doc Headers

Section Doc     Year    Nr
SEC1    PATAG   2017    7386

Doc Lines

Section Doc     Year    Nr      Line Type   Quantity
SEC1    PATAG   2017    7386    0           1.000000
SEC1    PATAG   2017    7386    4           2.000000

Query

SELECT
cab.strCodSeccao as [Section], cab.strAbrevTpDoc as [Doc], cab.strCodExercicio 
as [Year], cab.intNumero as [Nr], 
Cast (SUM (lin.fltQuantidade) as NUMERIC (15,2) ) as [Quantity],
CASE WHEN lin.intTpEntPagador = 0 THEN Cast (SUM (lin.fltQuantidade) as 
NUMERIC (15,2) ) ELSE 0 END as [Qtd Client],
CASE WHEN lin.intTpEntPagador = 4 THEN Cast (SUM (lin.fltQuantidade) as 
NUMERIC (15,2) ) ELSE 0 END as [Qtd Warranty]

FROM    
Mov_Apv_Reparacao_Lin as lin WITH (NOLOCK)
LEFT OUTER JOIN Mov_Apv_Reparacao_Cab as cab ON 
(lin.strCodseccao=cab.strCodSeccao AND Cab.strAbrevTpDoc=Lin.strAbrevTpDoc AND 
Cab.strCodExercicio=Lin.strCodExercicio AND Cab.intNumero=Lin.intNumero)

WHERE   
cab.strAbrevTpDoc='PATAG' AND cab.strcodexercicio = 
2017 and cab.intnumero = 7386

GROUP BY 
cab.strCodSeccao, cab.strAbrevTpDoc, cab.strCodExercicio, cab.intNumero, 
lin.intTpEntPagador

Result from Query

Section Doc     Year    Nr      Quantity    Qtd Client  Qtd Warranty
SEC1    PATAG   2017    7386    1.00        1.00        0.00
SEC1    PATAG   2017    7386    2.00        0.00        2.00

Desired result

Section Doc     Year    Nr      Quantity    Qtd Client  Qtd Warranty
SEC1    PATAG   2017    7386    3.00        1.00        2.00

Thanks in advance.

Upvotes: 0

Views: 96

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Try moving your SUM() outside of your CASE:

SELECT cab.strCodSeccao as [Section], 
       cab.strAbrevTpDoc as [Doc], 
       cab.strCodExercicio as [Year], 
       cab.intNumero as [Nr], 
       CAST(SUM(lin.fltQuantidade) as NUMERIC (15,2)) as [Quantity],
       CAST(SUM(CASE WHEN lin.intTpEntPagador = 0 
            THEN lin.fltQuantidade
            ELSE 0 
       END) AS NUMERIC (15,2)) as [Qtd Client],
       CAST(SUM(CASE WHEN lin.intTpEntPagador = 4 
            THEN lin.fltQuantidade
            ELSE 0 
       END) AS NUMERIC (15,2)) as [Qtd Warranty]

Side note, no need for DISTINCT here when you already have a GROUP BY

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

You have an attribute too much in your group by: you should remove lin.intTpEntPagador. That also means you have to change your case/summations:

Cast(SUM(CASE WHEN lin.intTpEntPagador = 0 THEN lin.fltQuantidade ELSE 0 END) as 
NUMERIC (15,2) ) as [Qtd Client]

You should also remove the distinct, it has no function if you also group by

Upvotes: 1

Related Questions