select count DISTINCT where != 2 filter

Question

I have a table that has eventid (alarm Event id) and eventtype (active = 1, Ack = 2, unack = 0). I would like to show a count of unacknowledged alarms.

SELECT count( DISTINCT eventid) FROM alarm_events where eventtype != 2

Not sure how i filter out the eventid that has been acknowledged

ha - Its my first time trying to use this forum. Sorry about that — Allen Ray, Dec 24 '15 at 04:37
Text is usually preferable to images when it comes to SQL data, because the former allows users to copy the data out. — Tim Biegeleisen, Dec 24 '15 at 04:44

score 2 · Answer 1 · edited May 23 '17 at 10:27

2

SELECT COUNT(DISTINCT eventid)
FROM alarm_events
WHERE eventid NOT IN (
    SELECT eventid
    FROM alarm_events
    WHERE eventtype = 2)

or equivalently

SELECT COUNT(DISTINCT a.eventid)
FROM alarm_events AS a
LEFT JOIN alarm_events AS b ON a.eventid = b.eventid AND b.eventtype = 2
WHERE b.eventid IS NULL

See Return row only if value doesn't exist

edited May 23 '17 at 10:27

Community

1
1

answered Dec 24 '15 at 04:52

Barmar

741,623
53
500
612

score 0 · Answer 2 · answered Dec 24 '15 at 05:06

0

SELECT count( DISTINCT eventid) FROM alarm_events where eventtype in (1,0)

try this

answered Dec 24 '15 at 05:06

Akshey Bhat

8,227
1
20
20

score 0 · Answer 3 · answered Dec 24 '15 at 05:40

0

You can do it using Group By and for filtering you can use IN operator.

SELECT count(eventid) FROM alarm_events WHERE eventtype in (0, 1) GROUP BY eventid

Pl replace (0, 1) with ('0','1') if your eventtype column is String.

answered Dec 24 '15 at 05:40

Rajen Raiyarela

5,526
4
21
41

select count DISTINCT where != 2 filter

3 Answers3