I am fairly new to SQL and am having some trouble with the last step in a slightly complicated SQL Query. I want to count how many times in the table, two distinct values appear in more than 1 row.
My specific scenario is, that my table stores Messages/Alerts too and from a system. These alerts are sent to multiple people who are required to respond. I want to count how many of the recipients responded to each Alert.
I have edited down my query to the part i am stuck on, which is this:
SELECT DISTINCT AlertID, count(RecipientID) - count(Distinct RecipientID) as Replies,
RecipientID MsgContents SentBy AlertID
12345 Msg1 mySystem 11111
98765 Msg1 MySystem 11111
12345 1st Reply to Msg1 John Doe 11111
12345 2nd Reply to Msg1 John Doe 11111
98765 reply to Msg1 Mike Smith 11111
12345 Msg3 mySystem 33333
12345 Reply to Msg3 John Doe 33333
12345 Msg2 mySystem 99999
You can do this using a
CASE expression inside
SELECT AlertID, Replies = COUNT(DISTINCT CASE WHEN SentBy <> 'mySystem' THEN RecepientID END) FROM alerts GROUP BY AlertID