sql server 'where' clause using outer sum.

I have my query returning the rows. I would like to add a where clause but dont know how to do that with this query

use Dictionary

	select sum(CountOfword) as TotalWordCount, word, Cat, sum(CountOfTagword) as TotalTagWordCount
from
(


select count(D.clientcodewordposition) AS CountOfword, D.word, count(TWT.clientcodewordposition) AS CountOfTagword, vc.VehCategory as Cat
	FROM TblWords as D LEFT JOIN TblWordTags AS TWT ON (D.ClientCodeWordPosition = TWT.ClientCodeWordPosition) LEFT JOIN [TblVehCat] as VC ON D.ClientCode = vc.ClientCode
	where D.word='Auto' 
	GROUP BY D.Word, vc.VehCategory
)a
***** tried here WHERE TotalWordCount>TotalTagWordCount ***-- THIS IS THE PART i DONT UNDERSTAND
GROUP BY Word, Cat
ORDER BY Word;

Open in new window

ee1.JPG
PeterBaileyUkAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you want is to filter by aggregate functions so you'll need a HAVING instead of WHERE:
use Dictionary

select sum(CountOfword) as TotalWordCount, word, Cat, sum(CountOfTagword) as TotalTagWordCount
from
(
select count(D.clientcodewordposition) AS CountOfword, D.word, count(TWT.clientcodewordposition) AS CountOfTagword, vc.VehCategory as Cat
	FROM TblWords as D LEFT JOIN TblWordTags AS TWT ON (D.ClientCodeWordPosition = TWT.ClientCodeWordPosition) LEFT JOIN [TblVehCat] as VC ON D.ClientCode = vc.ClientCode
	where D.word='Auto' 
	GROUP BY D.Word, vc.VehCategory
)a
GROUP BY Word, Cat
HAVING sum(CountOfword) >sum(CountOfTagword) 
ORDER BY Word;

Open in new window

0
 
PeterBaileyUkAuthor Commented:
great stuff thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.