CODEWITHSUNDEEP
postgresql
MCM13
MCM13

Reputation: 275

Count With Conditional on PostgreSQL

I have a table with people and another with visits. I want to count all visits but if the person signed up with 'emp' or 'oth' on ref_signup then remove the first visit. Example:

This are my tables:

PEOPLE:
   id    |  ref_signup
---------------------
   20    |   emp
   30    |   oth
   23    |   fri

VISITS
  id     |   date
-------------------------
  20     |   10-01-2019
  20     |   10-05-2019
  23     |   10-09-2019
  23     |   10-10-2019
  30     |   09-10-2019
  30     |   10-07-2019

On this example the visit count should be 4 because persons with id's 20 and 30 have their ref_signup as emp or oth, so it should exclude their first visit, but count from the second and forward.

This is what I have as a query:

SELECT COUNT(*) as visit_count FROM visits
LEFT JOIN people ON people.id = visits.people_id
WHERE visits.group_id = 1

Would using a case on the count help on this case as I just want to remove one visit not all of the visits from the person.

Upvotes: 2

Views: 14349

Answers (5)

Miles Elam
Miles Elam

Reputation: 1778

-- 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.

Upvotes: 1

Andy Carlson
Andy Carlson

Reputation: 3909

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;

Upvotes: 1

Celso Lívero
Celso Lívero

Reputation: 726

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

Upvotes: 1

Evan
Evan

Reputation: 2556

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

Upvotes: 1

forpas
forpas

Reputation: 164099

Subtract from COUNT(*) the distinct number of person.ids with person.ref_signup IN ('emp', 'oth'):

 SELECT 
  COUNT(*) - 
  COUNT(DISTINCT CASE WHEN p.ref_signup IN ('emp', 'oth') THEN p.id END) as visit_count 
FROM visits v LEFT JOIN people p
ON p.id = v.id

See the demo.
Result:

| visit_count |
| ----------- |
| 4           |

Note: this code and demo fiddle use the column names of your sample data.

Upvotes: 6

Related Questions