sqlvertica

Count district values based on another coulmn


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.


Solution

  • 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