Add a subtraction result to a query 1

[Michael57]

I have a query that produces a form and I would like to have the query put out a column that basically subtracts one column from the next. I would like to create a field that subtracts SUM (PMCATGT.CURQTY) AS BUDGETHRS from SUM (PMCATGT.ACTQTY) AS ACTUALHRS. I can't get this working.

My code is as follows:
Select PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE,SUM (PMCATGT.CURQTY) AS BUDGETHRS,SUM (PMCATGT.ACTQTY) AS ACTUALHRS,SUM (PMCATGT.CURCOSTHM) AS BUDGETCOST,SUM (PMCATGT.ACTCOSTHM) AS ACTUALCOST,MAX (PM.STATUS)
from STDDAT.dbo.PMCATGT PMCATGT
join STDDAT.dbo.PMCONTS PM on PMCATGT.CONTRACT=PM.CONTRACT
Where PM.STATUS=30
Group By PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE

 

[bborissov]

How about:

SELECT ...
       SUM(PMCATGT.ACTQTY - PMCATGT.CURQTY) AS ....
FROM ...
WHERE ...
GROUP BY....

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Be wary of NULLs and how they can make the following expressions yield different results:

SUM(PMCATGT.ACTQTY - PMCATGT.CURQTY)
SUM(PMCATGT.ACTQTY) - SUM(PMCATGT.CURQTY)
SUM(IsNull(PMCATGT.ACTQTY, 0) - IsNull(PMCATGT.CURQTY, 0)


[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]