CODEWITHSUNDEEP
sqlsql-server
Nuke
Nuke

Reputation: 1269

Filter rows by count of two column values

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

Upvotes: 5

Views: 1190

Answers (4)

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

gotqn
gotqn

Reputation: 43636

You can try this:

DECLARE @DataSource TABLE
(
   [MembershipNumber] SMALLINT
  ,[EmbossLine] SMALLINT
  ,[status] CHAR(2)
  ,[EmbossName] VARCHAR(8)
);

INSERT INTO @DataSource ([MembershipNumber], [EmbossLine], [status], [EmbossName])
VALUES (0009,0321,'E0','Finn')
      ,(0009,0322,'E1','Finn')
      ,(0004,0356,'E0','Mary')
      ,(0004,0398,'E0','Mary')
      ,(0004,0382,'E1','Mary');

SELECT [MembershipNumber]
      ,[EmbossLine]
      ,[status]
      ,[EmbossName]
FROM
(
    SELECT *
          ,COUNT([MembershipNumber]) OVER (PARTITION BY [EmbossName]) AS cnt1
          ,SUM(IIF([status] = 'E0' , 1, 0)) OVER (PARTITION BY [EmbossName]) AS cnt2
    FROM @DataSource
) DS
WHERE  cnt1 > 1 
    AND cnt2 > 1
    AND [status] = 'E0';

enter image description here

The idea is to add a second counter, but instead of COUNT function to use SUM function for counting only the rows that have [status] = 'E0'. Then, in the where clause we are filtering by the two counters and [status] = 'E0'.

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

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

Upvotes: 1

BufferUnderRun
BufferUnderRun

Reputation: 161

try :

WITH cnt AS (
    SELECT MembershipNumber, status
        FROM Card
        WHERE status = 'E0'
        GROUP BY MembershipNumber, status
        HAVING COUNT(MembershipNumber) > 1 AND COUNT(status) > 1
)
SELECT c.*
    FROM Card c
    INNER JOIN cnt
        ON c.MembershipNumber = cnt.MembershipNumber
           AND c.status = cnt.status;

Upvotes: 0

Related Questions