sum expression - not working correctly

[SQLScholar]

Hey all,

If i have a table (called table1). The columns are called `a` and `v`.

I want a query that sums all the values in `v` where the `a` col ='ab' then minus the ones that `ba`.

So i have a query like this

SELECT  'total' AS Expr1, ((select sum(`v`) from table1 where a='ab')-(select sum(`v`) from table1 where a='ba')) AS Expr2
FROM table1
;

What i am getting is correct but i am getting the same amount of rows as currently in the table... i really only want one. How would i do this?

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Crowley16]

try something like:

SELECT (SELECT SUM(a) FROM tbl1 WHERE v = 'ab') - (SELECT SUM(a) FROM tbl1 WHERE v = 'ba') AS total
FROM tbl1;

 

[SQLScholar]

I had to change the code abit to

SELECT (SELECT SUM(`v`) FROM table1 WHERE `a` = 'ab') - (SELECT SUM(`v`) FROM table1 WHERE `a` = 'ba') AS total
FROM table1;

but i am still getting 4! Any other ideas?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Crowley16]

are the 4 answers all correct?

if so, then just put a DISTINCT behind the 1st SELECT...

 

[PHV]

HAve you tried this ?
SELECT [highlight]DISTINCT[/highlight] 'total' AS Expr1, ((select sum(`v`) from table1 where a='ab')-(select sum(`v`) from table1 where a='ba')) AS Expr2
FROM table1
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

By adding Distinct to your query, the subqueries will still be calculated for each row in the table (even though you get the desired single row of output). You just need one pass through the table to calculate your sum:

Select Max("Total") as Total, Sum(IIf([a]="ab",[v],-[v])) as SumV
From Table1
Where [a] In ("ab", "ba")

 

[JonFer]

One more thought - if you want to use the subqueries, you should use a table in the FROM clause with just a few rows (or even a single row) and put a where clause that restricts that table to just one row.