Solved

Limiting rows returned from count of records in a subquery.

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Sever Import/export problem 7 49
Need help with convert Informix SQL SELECT statement to Microsoft SQL 1 26
SQL Syntax 6 41
SQL Server code help needed 14 33
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 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