How to get Absolute value in SQL? 
Author Message
 How to get Absolute value in SQL?

How do I get the absolute value from a field using SQL?

In the SQL below, SHAREAMOUT can be negative (sale), but since
the information I am gathering is a summary of transactions, the
average SHAREAMOUNT will get thrown off when it is negative.

select  SUM(SHARES) as TOTALSHARES,
SUM(SHARES * SHAREAMOUNT) as TOTALVALUE,
AVG(SHAREAMOUNT) as AVGSHARE
from TransactionHistory
group by Symbol

EG:

Symbol          ShareAmount
HMX             5
HMX            -5

Averages to 0.  What I want is 5--meaning of all the tranactions
the average per share value was 5.

Thanks

Matt



Sun, 23 May 1999 03:00:00 GMT  
 How to get Absolute value in SQL?

Matt:

how about something like
   max(shares,shares * -1) etc.???

Will that syntax work with your RDBMS????

-Dave


says...

Quote:

>How do I get the absolute value from a field using SQL?

>In the SQL below, SHAREAMOUT can be negative (sale), but since
>the information I am gathering is a summary of transactions, the
>average SHAREAMOUNT will get thrown off when it is negative.

>select  SUM(SHARES) as TOTALSHARES,
>SUM(SHARES * SHAREAMOUNT) as TOTALVALUE,
>AVG(SHAREAMOUNT) as AVGSHARE
>from TransactionHistory
>group by Symbol

>EG:

>Symbol          ShareAmount
>HMX             5
>HMX            -5

>Averages to 0.  What I want is 5--meaning of all the tranactions
>the average per share value was 5.

>Thanks

>Matt

--
Dave Krajcar, Alternative Solutions



Sun, 23 May 1999 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Absolute value without jumping (Was: Re: fast line routine continued)

2. Absolute value in assembler

3. Getting old record values on editing ..in TQuery

4. Getting ASCII Value of a Chr in a Str

5. Getting Value from current tquery record

6. Getting old record values on editing

7. Getting any cells value of a TCustomDBGrid

8. A class that gets field values from a TTable

9. Getting Return Value in Stored Procedures ??

10. Getting Min/Max/Default/Required Values from Paradox Tables

11. I'M GETTING MAD WITH VALUE PROPERTIE OF DBASE 5 WIN

 

 
Powered by phpBB® Forum Software