15,999,481 members
See more:
I have a Table as below

ID Condition Document Employee EID Person PID Owner Status
----------------------------------------------------------------------------
20 Condition1 Payoff 1 Processor 7 ccc Suzanne 1 LPTLC Close
29 Condition2 Bank St Processor 7 Herity 30 LPTLC Close
40 Condition4 Payoff 1 Processor 7 jack 37 LPTLC Close
65 Condition5 Paystub Processor 7 David 47 LPTLC Open
81 Condition1 IAPLD Processor 7 Herity 30 LPTLC open

I need a query output as count of person where EID=7 and Status =Close .
Thats mean these persons have only status as Close, not Open

Result as 2 from ccc Suzanne,jack.
Here Herity Has status open and close so it discard.
select row which person has status field only Close,this specified person has no status open.
How can we get?
Posted
Updated 8-Mar-11 19:46pm
v2

## Solution 2

Try this-

SQL
```Select Count(PID) from (
SELECT
PID
FROM
myTable
WHERE
EID = 7
group by PID
having Min(Status)=Max(Status) and  Min(Status) = 'Close'
) as SelectedData;```

Edited for getting total count[/Edit]

--Pankaj

v3
Then I got 0 row
how could you get 0 rows query using Count which will return at least 1 row either with value 0 or >0.
ok now i got your point change the group by EID to Group by PID. I already made changes in solution you can use this now.
When change it, return more than one rows . Top of row or sum of rows not correct.
could you please provide the output here.

## Solution 1

Try:
SQL
```SELECT
COUNT(ID)
FROM
myTable
WHERE
EID = 7
AND
Status = 'Close'```

What;s the problem with this?

I already used above query. It is not possible because of then we get count as 3, sum of all close status rows as ID 20,29,40.
But we need another condition as here ID 40,81 person as same ,Herity so it is not need. Herity has status open and close.
Sandeep Mewara 9-Mar-11 1:01am
Not clear! :doh:
Question is simple.
For example
We have more than 100 clients . Each client have more than 1 documents with status close or Open. That is main thing.
Now I required output is print number of clients , these clients all documents status is close.
eg? if a client have 1(or more) document with status open and 1(or more) document with status close. Then this client exact result is pending. So it is not take.Thats all