A - B = C, show all 3 columns ?

[SpookiePower]

I have this SQL -

SELECT (SUM(A) - SUM(B)) AS C
From table1

which shows the result, as it should do, but only shows C. What I would like to do, it to show all 3 results, like 1000 900 100

I have tried this -

SELECT ((SUM(A) AS A) - (SUM(B) AS B)) AS C

But it will not work as I want it to.

Anyone have an idea to the solution?

- Kim -

[AlexHedley]

You need them as separate fields

SELECT SUM(A) AS A, SUM(B) AS B, (SUM(A) - SUM(B)) AS C FROM table1

Original

SELECT (SUM(A) - SUM(B)) AS C FROM table1

[lfpm062010]

Alex is correct, except you can't use "AS A" and "AS B" again. The "A" and "B" are already define in Table.

Something like this will work.
SELECT Sum([A]) AS ASum, Sum([B]) AS BSum, (Sum([A])-Sum([B])) AS C
FROM table1;

[AlexHedley]

Good catch, wasn't thinking!

[SpookiePower]

Thanks to both of you I'll try it when I get home later to day

[Dal Jeanis]

Slightly more efficient to give it a different name anyway.

SELECT 
   MyGroup,
   Sum(A) As SumA, 
   Sum(B) as SumB, 
   SumA-SumB As SumC
FROM 
   MyTable
GROUP BY 
   MyGroup;

[SpookiePower]

Thanks It works