Solved

Limiting rows returned from count of records in a subquery.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

825 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