Arithmetic operators in MS Query 1

[daggers]

Hi,

I am attempting to extract the sum of the absolute values of table1.variable1 in MS Query.

i.e. i would like to this...

SELECT (Sum(ABS(table1.variable1)))
FROM table1
WHERE table1.variable1 <=0

but there is no built-in function that returns absolute numbers from a list of records. I also thought about doing this...

SELECT (Sum((table1.variable1*table1.variable1)^0.5))
FROM table1
WHERE table1.variable1 <=0

I was sure that MS Query had powers in its list of acceptable arithmetic functions, but when I run that the error message 'Invalid operator for datatype op: BOOLEAN XOR type: FLOAT' . Obviously clicking on 'Help' is totally useless!

Can anyone help....thanks

 

[iker3000]

Try this:

SELECT
SUM(
CASE
WHEN table1.variable1 > 0 THEN table1.variable
ELSE -1 * table1.variable1
END)
FROM
table1.variable1

OR this

SET @variable = (SELECT SUM(table1.variable1) FROM table1 WHERE variable1 > 0) + (SELECT SUM(table1.variable1) FROM table1 WHERE variable1 < 0)

Iker

 

[JamesLean]

You say you're using MS Query but I take it you are actually querying a SQL Server?

If so, just write your SQL directly into the &quot;view SQL&quot; window. When you click OK, it may warn you that it can't display the query but when you actually execute it, it will still work fine.

SELECT SUM(ABS(c1))
FROM t1

--James