Sum Positive and negative values in same Field (1 Viewer)

[DaRTHY]

Hello friends;

Im trying to get sum "+" and "-" Values in same Field. I created new Expression Builder in Query and i wrote there :

A: Sum([tablename]<0)

of course it did not work.

how shall i update my expression to work this thing ?

Ty for your helps.

 

[gemma-the-husky]

you probably need a query to select the negative items

sum(amount) where amount<0

but you won't get both in the same query with that.

you could add a group by with this sort of syntax. This will give you totals of each group, and you would have 2 groups, positive and negative. I am not used to writing SQL directly, but this sort of thing.

sum(amount) groupby (amount>0)

 

[vbaInet]

Not sure I follow, but if you want to add both the positive and negative numbers then you just use Sum([TheField])? Or they separate fields?

 

[DaRTHY]

okey. I guess i wrote there wrong. I have a field and there + and - values.

i want to sum up negative value with other negative values and positive value with other positive values.

And i ll use these in Bar Chart after i sum these Values. For bar chart i shall separate positive and negative values. (at the same time i want to sum them too...)

hope so i could explain myself better at this time. Im sending as a picture my field.

 

[vbaInet]

* First you want to identify the positive and the negative values in a field, something like:

ValueType: IIF([Field] < 0, "Negative", "Positive")

... I'm guessing you class 0 as a positive value.

* Next you click the Totals button and Sum() the field GROUPing BY ValueType.

 

[Galaxiom]

SELECT Sgn(amount) AS Sign, Sum(amount) AS SumOfAmount
FROM table
GROUP BY Sgn(amount)

 

[vbaInet]

Good call with Sign Galaxiom, almost forgetting my trig.

 

[DaRTHY]

im so bad to explain myself or im so bad to understand. (after 7 hours nonstop working. its really hard to write smthing as english for me.

for my picture there are 2 - Value and
-152.400,00+(-70,00) = -152.470,00 this is for negative...
same thing for positive too 3.525,00+36.941,00+462.083,00 = 502.549,00
and these results i need in new Field thats why i used Build expression.

sry if i steal your time for my bad.

 

[vbaInet]

Take your time and study posts #5 and #6. The answers are there.