asked on

SQL Server Count where two id types exist in column

I have a simple table
IndividualID DirectionID
123 1
123 2
129 1
130 1

I a result with IndividualID and if it has at LEAST one instance of a 1 and a 2 with a Bit result

Data able
Would return this
IndividualID TwoWatCOnversation
123 1
129 0
130 0

Pawan Kumar

Hi Irbrister,
Please try this - Full and tested solution for you-

--

CREATE TABLE Bits
(
	 IndividualID     INT 
	,DirectionID INT
)
GO

INSERT INTO Bits VALUES
(123          ,                  1	 ),
(123          ,                  2	 ),
(129          ,                  1	 ),
(130          ,                  1	 )
GO

SELECT IndividualID , CASE WHEN cnt > 1 THEN 1 ELSE 0 END TwoWatCOnversation
FROM 
(
	SELECT DISTINCT IndividualID , COUNT(*) OVER (PARTITION BY IndividualID) cnt FROM Bits
)k

--

Open in new window

Output

/*------------------------

SELECT IndividualID , CASE WHEN cnt > 1 THEN 1 ELSE 0 END TwoWatCOnversation
FROM 
(
	SELECT DISTINCT IndividualID , COUNT(*) OVER (PARTITION BY IndividualID) cnt FROM Bits
)k
------------------------*/
IndividualID TwoWatCOnversation
------------ ------------------
123          1
129          0
130          0

(3 row(s) affected)

Open in new window

Hope it helps!

Pawan Kumar

or this -

SELECT IndividualID , CASE WHEN cnt > 1 THEN 1 ELSE 0 END TwoWatCOnversation
FROM 
(
	SELECT IndividualID , COUNT(*) cnt FROM Bits
	GROUP BY IndividualID
)k

Open in new window

OUTPUT

/*------------------------
SELECT IndividualID , CASE WHEN cnt > 1 THEN 1 ELSE 0 END TwoWatCOnversation
FROM 
(
	SELECT IndividualID , COUNT(*) cnt FROM Bits
	GROUP BY IndividualID
)k
------------------------*/
IndividualID TwoWatCOnversation
------------ ------------------
123          1
129          0
130          0

(3 row(s) affected)

Open in new window

Hope it helps!

Larry Brister

ASKER

Pawar...
That LOOKS right but doesn't include the logic that it MUST have a DirectionID = 1 AND a DirectionID = 2

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Edited my last comment

Larry Brister

ASKER

Hey guys...
You put me on the right track
This is my final solution

SELECT a.IndividualID ,
CASE WHEN b.cnt = 2 THEN 1
ELSE 0
END TwoWayC0nversation,
c.indCount
FROM ewMessages a
LEFT JOIN ( SELECT IndividualID ,
COUNT(DirectionID) cnt
FROM ewMessages
WHERE DirectionID IN ( 1, 2 )
GROUP BY IndividualID
) b ON a.IndividualID = b.IndividualID
LEFT JOIN ( SELECT IndividualID ,
COUNT(IndividualID) indCount
FROM ewMessages
WHERE DirectionID IN ( 1, 2 )
GROUP BY IndividualID
) c ON a.IndividualID = c.IndividualID
WHERE SalesSiteID IN ( 102816, 103052, 103053, 103054 )
GROUP BY a.IndividualID ,
c.indCount,
CASE WHEN b.cnt = 2 THEN 1
ELSE 0
END
ORDER BY a.IndividualID;