Union Query Result Sum

[Preetham16]

Hello Gurus,

Following Code is part of UNION QUERY ... after the UNION i would like to have the SUM of Value of the Columns
ABSMG (quantity)
VVBUM (Gross Sales)
VVNUM (Net Sales)

I am getting an error when I directly use a SUM FUNCTION
at the following line of CODE as shown below

replace(format(SUM(a.WRT_RETOURE * -1,"#.00")),",",".") AS VVBUM,


------------------------------------------

(select count(*) from HIST_V d
where d.PERIO = a.PERIO
and d.typ = a.typ
and d.KUNNR = a.KUNNR)) AS POSNR,
"5800" as WERKS,
"5800" as BUKRS,
"5800" as KOKRS,
"F" as VRGAR,
a.perio & '01' as FADAT,
a.perio & '01' as BUDAT,
mid(a.perio,1,4) as GJAHR,
"0" & mid(a.perio,5,2) as PERDE,
mid(a.perio,1,4) & "0" & mid(a.perio,5,2) as PERIO,
"0" as PLIKZ,
format(a.matnr,"000000000000000000") as ARTNR,
" " as UPMAT,
"5800" as VKORG,
"MX" as VTWEG,
"MX" as SPART,
format(a.KUNNR,"0000000000") as KUNWE,
format(a.KUNNR,"0000000000") as KNDNR,
"ZARE" AS FKART,
replace(format(a.MNG_RETOURE * -1, "#.000"),",",".") as VVBMG,
'ST' as VVBMG_ME,
replace(format(a.MNG_RETOURE * -1, "#.000"),",",".") as ABSMG,
'ST' as ABSMG_ME,
replace(format("0", "0.00"),",",".") as VVNUM,
replace(format(a.WRT_RETOURE * -1,"#.00"),",",".") AS VVBUM,
replace(format(a.mng_RETOURE * a.verpr * -1,"0.00" ),",",".") as VVVPR,
replace(format("0", "0.00"),",",".") as PRABA,
"ZARE" as AUART,
"REN" as PSTYV,
"MXN" as FRWAE,
"" as WWGGR
FROM HIST_V as a
WHERE (((a.MNG_RETOURE)<>0) AND ((a.WRT_RETOURE)<>0))
and a.PERIO = '200508';


Highly appreciated if some one can help me in this regard

PR

 

[lameid]

Your syntax error is a misplaced parenthesis... You need to sum the number before formatting it.

replace(format(SUM(a.WRT_RETOURE * -1),"#.00"),",",".") AS VVBUM

You mention this being in a Union. This will only sum from this part of the select and not the entire union query.

If you need to sum over the entire union, you would have to remove the replace and format functions from the query and then base a query on the union query to perform the sum and then format it similar to above.