I have the following query which works well:SELECT DISTINCT sgsID, sgTitle, COALESCE(COUNT(sgsID),0) as [allocationCount], COALESCE(COUNT(notApplicable),0) as [notApplicableCount]FROM @tmpTableGROUP BY sgsID, sgTitle
Within the query however, I need to also show the [allocationCount] column less the [notApplicableCount] column. I tried the following but it doesn't work:SELECT DISTINCT sgsID, sgTitle, COALESCE(COUNT(sgsID),0) as [allocationCount], COALESCE(COUNT(notApplicable),0) as [notApplicableCount], ([allocationCount] - [notApplicableCount]) as [total] /* <-- Invalid column name 'allocationCount'. */FROM @tmpTableGROUP BY sgsID, sgTitle
Could anyone please englighten me as to how this is achieved please?