Solved

Limiting rows returned from count of records in a subquery.

Posted on 2014-03-26
3
149 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
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 65

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

20 Experts available now in Live!

Get 1:1 Help Now