Count district values based on another coulmn-CodePudding

I have an sql table of people with their event attendance status:

Person	EventStatus
Ethan Williams	NoShow
Ethan Williams	NoShow
Olivia Rodriguez	Arrived
Olivia Rodriguez	NoShow
Olivia Rodriguez	NoShow
Benjamin Chen	Arrived
Benjamin Chen	NoShow
Isabella Gomez	NoShow

I'm trying to filter and count only the people who never came to any event but can't get the right code to do this.

I was able to get this result using this code:

SELECT "Person" as "Person"
FROM TABLE
GROUP BY "Person"
HAVING (((COUNT(DISTINCT CASE
                         WHEN EventStatus = 'NoShow' THEN 1
                         ELSE NULL
                     END) - COUNT(DISTINCT CASE
                         WHEN EventStatus = 'Arrived' THEN 1
                                               ELSE NULL
                                           END)) > 0))

But using "GROUP BY" won't allow me to count the results.

The expected result I need is: 2 Which is the result these 2 people who are always "NoShow": Isabella Gomez, Ethan Williams.

CodePudding user response：

People who never showed = all people - people who ever showed.

SELECT
  COUNT(DISTINCT person)
  -
  COUNT(DISTINCT CASE WHEN EventStatus = 'Arrived' THEN person END)
FROM
  yourTable

CodePudding user response：

One approach uses aggregation along the lines of what you were trying:

WITH cte AS (
    SELECT Person
    FROM yourTable
    GROUP BY Person
    HAVING COUNT(CASE WHEN EventStatus = 'Arrived' THEN 1 END) = 0
)

SELECT COUNT(*) AS cnt
FROM cte;