Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

Limiting rows returned from count of records in a subquery.

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
BernardGBailey
Asked:
BernardGBailey
1 Solution
 
GowthamNatarajanCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
BernardGBaileyAuthor Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now