Filter rows by count of two column values

Question

I have following table:

Card(
  MembershipNumber,
  EmbossLine,
  status,
  EmbossName
)

with sample data

(0009,0321,'E0','Finn')
(0009,0322,'E1','Finn')
(0004,0356,'E0','Mary')
(0004,0398,'E0','Mary')
(0004,0382,'E1','Mary')

I want to retrieve rows such that only those rows should appear that have count of MembershipNumber > 1 AND count of status='E0' > 1.

For Example The query should return following result

(0004,0356,'E0','Mary')
(0004,0398,'E0','Mary')

I have the query for filtering it with MembershipNumber count but cant figure out how to filter by status='E0'. Here's the query so far

SELECT *
FROM   (SELECT *,
               Count(MembershipNumber)OVER(partition BY EmbossName) AS cnt
        FROM   card) A
WHERE  cnt > 1 

Answer 1

You can just add WHERE status = 'E0' inside your subquery:

SQL Fiddle (credit to Raging Bull for the fiddle)

SELECT *
FROM (
    SELECT *,
        COUNT(MembershipNumber) OVER(PARTITION BY EmbossName) AS cnt
    FROM card
    WHERE status = 'E0'
)A
WHERE cnt > 1 

Answer 2

You can do it this way:

select t1.*
from card t1 left join
    (select EmbossName
     from card
     where [status]='E0'
     group by EmbossName,[status]
having count(MembershipNumber)>1 ) t2 on t1.EmbossName=t2.EmbossName
where t2.EmbossName is not null and [status]='E0'

Result:

MembershipNumber    EmbossLine  status  EmbossName
---------------------------------------------------
4                   356         E0      Mary
4                   398         E0      Mary

Sample result in SQL Fiddle