I can not sum the column

1

I want to sum the Value column, I know that in order to use SUM () I have to use GROUP BY as well, but I can not do the summation. I do not know what fields I have to put in GROUP BY.

SELECT DISTINCT z.sales_office AS kam, z.customer,g.sales_order,
d.nfnum, d.vlr_liq_nf AS valor FROM TPL_GUARDA g 
LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 
where c.dt_exp is null and d.nfnum is not null and z.delivery is not null 
GROUP BY
    
asked by anonymous 25.09.2018 / 14:44

3 answers

1

Basically, group by should enter all fields that were not sum :

SELECT DISTINCT z.sales_office AS kam, z.customer, g.sales_order, d.nfnum, SUM(d.vlr_liq_nf) AS valor
FROM TPL_GUARDA g 
LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 
WHERE c.dt_exp IS NULL
  AND d.nfnum IS NOT NULL
  AND z.delivery IS NOT NULL
GROUP BY z.sales_office, z.customer, g.sales_order, d.nfnum

In comment it was said that the field to be added is of type varchar2 . Ensuring that they would still be saved as text, you can use to_number :

SUM(TO_NUMBER(d.vlr_liq_nf))

As quoted in comment, the values in the database were being separated by commas, which is not accepted by oracle as a valid number; it would then suffice to give a replace and change the comma to a period (accepted character):

SUM(REPLACE(d.vlr_liq_nf,',','.')) 
    
25.09.2018 / 14:49
0

Use the SUM function that will solve your problem.

SELECT SUM (d.vlr_liq_nf) AS valor
FROM TPL_GUARDA g 
LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 

This will return only the result you need, the sum of the values in that column. Make sure the column type is numeric.

    
25.09.2018 / 15:51
0

I was able to solve the problem, in oracle, the comma is different, comma does not mean comma. It was giving the Invalid Number error, because of the comma, oracle could not do the conversion. So I used replace to replace the comma by one point, and I used another select so I did not have to use group by. Here is the code working:

 SELECT SUM(REPLACE(valor,',','.')) FROM (SELECT DISTINCT z.sales_office AS 
 kam, z.customer,g.sales_order,
 d.nfnum, d.vlr_liq_nf AS valor FROM TPL_GUARDA g 
 LEFT JOIN TPL_COLETAS c     ON g.SALES_ORDER   = c.SALES_ORDER 
 LEFT JOIN humo h            ON h.HANDLING_UNIT = g.handling_unit 
 LEFT JOIN DANFE_DELIVERY dd ON dd.delivery     = h.delivery 
 LEFT JOIN danfe d           ON d.nfnum         = dd.nfnum 
 LEFT JOIN zzcustmon z       ON z.sales_order   = g.sales_order 
 where c.dt_exp is null and d.nfnum is not null and z.delivery is not null)
    
26.09.2018 / 16:24