Solved

sql server 'where' clause using outer sum.

Posted on 2016-09-05
2
22 Views
Last Modified: 2016-09-05
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
0
Comment
Question by:PeterBaileyUk
2 Comments
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:PeterBaileyUk
Comment Utility
great stuff thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now