Solved

Limiting rows returned from count of records in a subquery.

Posted on 2014-03-26
3
153 Views
Last Modified: 2014-03-30
Hi Team,

I am trying to nut out a query which returns the COUNT of rows in a history table where a resultcode is 'NOT' and that COUNT is more than 2.

I have been sweeping the Net to find a clue, alas without luck.

EG

Select KEY1,Company, (select COUNT(resultcode) from C1)
from CONTACT1
inner join CONTACT2 on Contact1.ACCOUNTNO = CONTACT2.ACCOUNTNO
where Exists (select key1,COUNT(RESULTCODE)
from CONTACT1 c1
inner join CONTACT2 c2 on C1.ACCOUNTNO = C2.ACCOUNTNO
left join CONTHIST CH on CH.ACCOUNTNO = C1.ACCOUNTNO
where
CH.RESULTCODE = 'SHD'
GROUP BY C1.KEY1)

I get this error   "Invalid object name 'C1'"

All help is achknowledged.
Cheers
Bernard
0
Comment
Question by:BernardGBailey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 2

Accepted Solution

by:
GowthamNatarajan earned 500 total points
ID: 39958190
select T.KEY1,T.Company,T.CountFromHistory from (Select CONTACT1.KEY1,CONTACT1.Company, (select COUNT(CH.ResultCode) from CONTHIST CH where CH.Acctno = CONTACT1.AcctNo and CH.resultCode = 'SHD')  AS CountFromHistory
from CONTACT1
inner join CONTACT2 on CONTACT1.ACCOUNTNO  = CONTACT1.ACCOUNTNO) T where T.CountFromHistory > 2
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39958804
>and that COUNT is more than 2.
To filter on an aggreate you need a HAVING clause, not a WHERE clause.

Select KEY1,Company, (select COUNT(resultcode) from C1)
from CONTACT1 
	inner join CONTACT2 on Contact1.ACCOUNTNO = CONTACT2.ACCOUNTNO
where Exists (
	select key1,COUNT(RESULTCODE)
	from CONTACT1 c1
		inner join CONTACT2 c2 on C1.ACCOUNTNO = C2.ACCOUNTNO
		left join CONTHIST CH on CH.ACCOUNTNO = C1.ACCOUNTNO
		where CH.RESULTCODE = 'SHD'
		GROUP BY C1.KEY1
		HAVING count(resultcode) > 2)   -- < --  Something like this

Open in new window

There's an article out there on SQL Server GROUP BY Solutions; scroll down to #3 'Filter rows by a condition on an aggregate value:  HAVING'.
0
 

Author Closing Comment

by:BernardGBailey
ID: 39965251
GowthamNatarajan,

I was able to get this query going from your code after making a change.  See under

select T.KEY1,T.Company,T.NOTCnt
from (Select CONTACT1.KEY1,CONTACT1.Company,
(select COUNT(CH.ResultCode) from CONTHIST CH
left join CONTACT2 c2 on c2.ACCOUNTNO = CH.ACCOUNTNO
where CH.Accountno = CONTACT1.AccountNo and CH.resultCode = 'NOT'
and CH.ONDATE > ULASTSALE
and contact1.key5 ='GREEN'
) AS NOTCnt
from CONTACT1
inner join CONTACT2 on CONTACT2.ACCOUNTNO  = CONTACT1.ACCOUNTNO) T where T.NOTCnt > 2

 Many thanks


Jim, Sorry I had no luck making your solution work.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

688 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