Count With Conditional on PostgreSQL

Premise, select the count of visits from each person, along with a synthetic column that contains a 1 if the referral was from emp or oth, a 0 otherwise. Select the sum of the count minus the sum of that column.

SELECT SUM(count) - SUM(ignore_first) FROM  (SELECT COUNT(*) as count, CASE WHEN ref_signup in ('emp', 'oth') THEN 1 ELSE 0 END as ignore_first as visit_count FROM visits
LEFT JOIN people ON people.id = visits.people_id
WHERE visits.group_id = 1 GROUP BY id) a

where's "people_id" in your example ?

SELECT COUNT(*) as visit_count 
FROM visits v
JOIN people p ON p.id = v.people_id 
WHERE p.ref_signup IN ('emp','oth');

then remove the first visit.

You cannot select count and delete the first visit at same time.

DELETE FROM visits 
  WHERE id IN (
      SELECT id 
      FROM visits v
      JOIN people p ON p.id = v.people_id 
      WHERE p.ref_signup IN ('emp','oth')
      ORDER BY v.id
      LIMIT 1
);

edit: typos

First, I create the tables

create table people (id int primary key, ref_signup varchar(3));
insert into people (id, ref_signup) values (20, 'emp'), (30, 'oth'), (23, 'fri');
create table visits (people_id int not null, visit_date date not null);
insert into visits (people_id, visit_date) values (20, '10-01-2019'), (20, '10-05-2019'), (23, '10-09-2019'), (23, '10-10-2019'), (30, '09-10-2019'), (30, '10-07-2019');

You can use the row_number() window function to mark which visit is "visit number one":

select
  *,
  row_number() over (partition by people_id order by visit_date) as visit_num
from people
join visits
  on people.id = visits.people_id

Once you have that, you can do another query on those results, and use the filter clause to count up the correct rows that match the condition where visit_num > 1 or ref_signup = 'fri':

-- wrap the first query in a WITH clause
with joined_visits as (
  select
    *,
    row_number() over (partition by people_id order by visit_date) as visit_num
  from people
  join visits
    on people.id = visits.people_id
)
select count(1) filter (where visit_num > 1 or ref_signup = 'fri')
from joined_visits;

-- First get the corrected counts for all users
WITH grouped_visits AS (
  SELECT
    COUNT(visits.*) -
      CASE WHEN people.ref_signup IN ('emp', 'oth') THEN 1 ELSE 0 END
      AS visit_count
  FROM visits
    INNER JOIN people ON (people.id = visits.id)
  GROUP BY people.id, people.ref_signup
)
-- Then sum them
SELECT SUM(visit_count)
FROM grouped_visits;

This should give you the result you're looking for.

On a side note, I can't help but think clever use of a window function could do this in a single shot without the CTE.

EDIT: No, it can't since window functions run after needed WHERE and GROUP BY and HAVING clauses.