12 Replies Latest reply: Dec 21, 2016 10:15 AM by Clever Anjos

# Problem with function SUM()

Hi everyone,

I finally end my code (hope so), and thanks to all that spend some time to help me.

Now the last problem that i dont understand is using the SUN() function in the graphic.

If you look the image for the meter 32646 i only have 1 value 0,17 so when i use SUN(CONSUMO_REAL0), I expect output 0,17.

Why for all saints im getting 2,04 ????

• ###### Re: Problem with function SUM()

Cus it is doing sum for all related OID_METER 32646 for CONSUMO_REAL0.

Maybe try Sum(Distinct(CONSUMO_REAL0) ???

• ###### Re: Problem with function SUM()

Yah, but by the image i have only 4 measures to this meter, 1 =0,17 and 3 = null.

How this sum could be equal 2,04?

• ###### Re: Problem with function SUM()

I don't know how your data is or script is....

Maybe try what Sunny is saying in below reply?

• ###### Re: Problem with function SUM()

It seems that your data is repeating for some reason. In this particular case the OID_METER might be repeating 12 times (12*0.17 = 2.04). Although you can fix this on the front end of the application, I would suggest fixing this in the back end because you don't want to carry a single row 12 times if you don't have a real use for it

• ###### Re: Problem with function SUM()

Hey Sunny, i saw this 12*0.17, but still dont get why this happen.

About carry a single row 12 times if i dont use SUN() its not occur. I think that will be better use Max() instead SUN()

• ###### Re: Problem with function SUM()

You have many options, you can do Only(), Max(), Avg(), Min(), Sum(Distinct...).... but all these are workarounds. None of them solves the underlying issue that you have multiple rows with same information.

• ###### Re: Problem with function SUM()

I agree about thats only workarounds, but why not apear that repeated values when i dont use any agreggation?

• ###### Re: Problem with function SUM()

Because a table only show a unique combination of all the fields. If you add another field where the two rows would not match, you will see your multiple rows.

• ###### Re: Problem with function SUM()

12 times sounds me related to calendar problem (12 months) do you have a calendar into your app?

Does anytime you make a join into your script?

• ###### Re: Problem with function SUM()

Hi Clever,

More or less, I get some date values, but not using a calendar.

ANALISE_TMP:

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

OID_METER,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

RESIDENT TMP2

DROP TABLE TMP,TMP2;

Exclusao:

OID_METER,

EXCLUDE_METER

RESIDENT ANALISE_TMP

WHERE NOT EXISTS (EXCLUDE_METER,OID_METER);

INNER JOIN (Exclusao)

OID_METER,

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

CONSUMODIA0,

CONSUMODIA1,

CONSUMODIA2,

If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA0)- CONSUMODIA0) AS CONSUMO_REAL0,

If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA1)- CONSUMODIA1) AS CONSUMO_REAL1,

If (OID_METER =PEEK(OID_METER), PEEK(CONSUMODIA2)- CONSUMODIA2) AS CONSUMO_REAL2,

HORA

RESIDENT ANALISE_TMP

WHERE   (HOUR(HORA)=2 OR HOUR(HORA)=5)

ORDER BY OID_METER DESC;

DROP TABLE ANALISE_TMP;

Teste:

OID_METER,

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

DATA,

CONSUMO_REAL0,

CONSUMO_REAL1,

CONSUMO_REAL2,

HORA AS HORA_2

RESIDENT Exclusao

ORDER BY OID_METER DESC;

DROP TABLE Exclusao;

EXIT SCRIPT

• ###### Re: Problem with function SUM()

Try with this (if make sense)

Teste:

OID_METER,

ID_LEITURA,

NAME_SYSTEM,

NAME_GROUP,

NEIGHB_SYSTEM,

CITY_SYSTEM,

NAME_TYPE_METER,

TYPE_CONSTANT,

NAME_UC,

DATA,

CONSUMO_REAL0,

CONSUMO_REAL1,

CONSUMO_REAL2,

HORA AS HORA_2

RESIDENT Exclusao

ORDER BY OID_METER DESC;

• ###### Re: Problem with function SUM()

Hi,

The reason for repetiton is due the underlying data modelling.

1) the table which contains OID_METER,Consumo_real0 columns might be joined with other tables, hence causing duplicate records

2)the columns OID_METER and Consumo_real0 might be in different tables and are associated using a common key which might have 12 entries.