sum up the positive numbers in a column

[megatronixs]

Hi all,



I have a query running that gets data from a linked table. There is a column in the table that has negative numbers and positive number in it. I would like to sum up the positive numbers and show the rusults at the end of the query results.
How to go about this in a query? the column/field is called [ammount_details]

Greetings.

[orange]

Here's the general sql involved to get sum of ammount_details where values are >0. You need to supply the proper table name.

SELECT Sum(ammount_details) AS SumOfammount_details
FROM YourTableNameHere
HAVING (((Sum(ammount_details))>0));

[InsuranceGuy]

Here's the general sql involved to get sum of ammount_details where values are >0. You need to supply the proper table name.

SELECT Sum(ammount_details) AS SumOfammount_details
FROM YourTableNameHere
HAVING (((Sum(ammount_details))>0));

Hi orange, I think you need to use a where condition instead of a having condition. Having will check after the sum. Instead:

select sum(amount) from table where [amount]>0

Cheers,

Jeff

[orange]

Good point. I built the query with Access query designer using Totals (sigma) and got the original SQL.

You are correct, the WHERE clause will evaluate the positive/negative of each ammount_details before summing.

So proper sql

SELECT Sum(ammount_details) AS SumOfammount_details
FROM YourTableNameHere
WHERE  (ammount_details) >0 ;

[megatronixs]

Hi all,

Thanks a lot. I was a few days absent and had not chance to check it out.

Greetings.