Geek Answers Handbook

The number of times in the SQL table is displayed in more than 1 row.

I am new to SQL and I have some problems with the last step in a bit complicated SQL Query. I want to count how many times in the table, two different values ​​are displayed in more than 1 row.

My specific scenario is that my table also stores messages / warnings and the system. These alerts are sent to several people who need to respond. I want to calculate how many of the recipients answered each alert.

I edited my request to the part I'm stuck on, here's what:

SELECT DISTINCT AlertID, count(RecipientID) - count(Distinct RecipientID) as Replies, 
FROM [myDB].[dbo].[Alerts]

This query shows how many messages each notification contains, not including the original message for each recipient from the system. The problem is that if someone responds twice to one warning, he counts as two answers, since they refer to 1 (this is what I want).

I thought I could do this by counting each DISTINCT AlertID and how many times the DISTINCT recipient IDs appear with AlertID in more than 1 line. It doesn’t matter if someone answered twice, because if there are more than one, he is considered the only answer. I have problems with work.

My data is as follows:

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 query result to be:

 AlertID        Replies
 11111          2
 33333          1
 99999          0

, , . , , . , .

.

+4
3

, CASE COUNT.

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

-

+3

-, select distinct, group by.

, , , :

SELECT AlertID, (count(Distinct RecipientID) - 1) as Replies
FROM [myDB].[dbo].[Alerts]   
GROUP BY AlertId;

, :

SELECT AlertID, count(Distinct case when sentBy <> 'mySystem' then RecipientID) as Replies 
FROM [myDB].[dbo].[Alerts]   
GROUP BY AlertId;
+1
select AlertId, count(RecipientId) as Replies group by AlertId 
from (select * from Alerts where SentBy <> 'mySystem')

-

- The second line receives records for actual recipients, unlike system records

0
source

More articles:


All Articles