I’m having trouble summing up the column

Question

I want to sum up the column Value, I know that to use SUM() I also have to use GROUP BY, only I’m not able to make the sum. I don’t know which fields I have to put on 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

Answer 1

Basically, in the group by should enter all fields that were not the 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 the type varchar2. Ensuring that they would still be saved whole as text, you can use the 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 by a dot (accepted character):

SUM(REPLACE(d.vlr_liq_nf,',','.')) 

Answer 2

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 way, you will return only the result you need, the sum of the values of this column. Check the column type is numerical.

Answer 3

I managed to solve the problem, in the oracle, the comma treatment is different, comma does not mean comma. It was giving the error Invalid Number, because of the comma, the oracle could not do the conversion. So I used replace to replace the comma with a dot, and I used another select so that I didn’t have to use group by. Follow 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)