mike100111 mike100111 - 6 months ago 13
SQL Question

Count number of times 2 distinct values appear in more than 1 row in SQL Table

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,
FROM [myDB].[dbo].[Alerts]


This query shows how many messages each alert has in it, not including the original message to each recipient from the system. The issue with this is that if someone responds twice to a single alert, it is counted as two responses as apposed to 1(which is what i want).

I thought i could do this by counting each DISTINCT AlertID and how many times a DISTINCT recipients ID appears with the AlertID in more than 1 row. It wouldn't matter if someone responded twice, because as long as there is more than one it is counted as a single response. I'm having trouble getting this to working.

My data looks like this:

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


I would like the output of the query to be:

AlertID Replies
11111 2
33333 1
99999 0


I thought this answer would help, but couldn't get it to work for me. I also would like to keep it in one query if possible, as the system will be dealing with large amounts of data. If this is not the easiest way to do it, i am open to all suggestions.

Any help would be greatly appreciated.

Answer

You can do this using a CASE expression inside COUNT.

SELECT
    AlertID,
    Replies = COUNT(DISTINCT CASE WHEN SentBy <> 'mySystem' THEN RecepientID END)
FROM alerts
GROUP BY AlertID

ONLINE DEMO