Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to perform math against two COUNT columns

Author  Topic 

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-12-07 : 03:05:00
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
@tmpTable
GROUP 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
@tmpTable
GROUP BY
sgsID,
sgTitle


Could anyone please englighten me as to how this is achieved please?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-07 : 03:18:07
Is this you are looking for :
SELECT
DISTINCT sgsID,
sgTitle,
COALESCE(COUNT(sgsID),0) as [allocationCount],
COALESCE(COUNT(notApplicable),0) as [notApplicableCount],
COALESCE(COUNT(sgsID),0) - COALESCE(COUNT(notApplicable),0) as [total] /* <-- Invalid column name 'allocationCount'. */
FROM
@tmpTable
GROUP BY
sgsID,
sgTitle
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 04:36:41
Or by using a derived table:

SELECT *, ([allocationCount] - [notApplicableCount]) as [total]
FROM (
SELECT
DISTINCT sgsID,
sgTitle,
COALESCE(COUNT(sgsID),0) as [allocationCount],
COALESCE(COUNT(notApplicable),0) as [notApplicableCount]
FROM
@tmpTable
GROUP BY
sgsID,
sgTitle
) as dt


- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

R
Constraint Violating Yak Guru

328 Posts

Posted - 2010-12-07 : 05:04:10
Okay great, thank you. Please tell me, does this:


COALESCE(COUNT(sgsID),0) - COALESCE(COUNT(notApplicable),0) as [total] /*


use more resources that the derived table? I would assume that the COUNTS are being done twice here...?
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-07 : 05:17:58
To be honest I've never been sure if using the same statements multiple times means multiple operations or not. But to make absolutely sure it isn't I always use derived tables. I personally think that it increases readability as well.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
   

- Advertisement -