Query return only once time participant in year-CodePudding

How to create a query SQL for Postresql who select only those people who have never participated in previous events and only once this year, as in the last table below.

tbl_evt

id_evt	evt_date
1	2022-10-01
2	2022-08-05
3	2021-01-01
4	2020-06-05

tbl_people_evt

id_people	id_evt
1	1
1	4
2	1
3	1
3	3
4	1
5	1
5	2
6	3

Searched result

id_people
2
4
5

CodePudding user response：

I got it, but if anyone has another solution

SELECT
    COUNT(p.cod_people)
FROM
    tbl_evt e
INNER JOIN tbl_people_evt p ON
    p.id_evt = e.id_evt
WHERE
    date_part('year', e.evt_date) = '2022'
    AND p.id_people IN (
    SELECT
        p.id_people
    FROM
        tbl_people_evt p
    INNER JOIN tbl_evt e ON
        p.id_evt = e.id_evt
    GROUP BY
        p.id_people
    HAVING
        COUNT(p.id_people) = 1
)

CodePudding user response：

So if "only those people who have never participated in previous events and only once this year" means

previous events - events before 2022
this year - events in 2022

Then

select p.id_people,
       sum(case when date_part('year', e.evt_date) = 2022 then 1 else 0 end) as cnt_2022,
       sum(case when date_part('year', e.evt_date) < 2022 then 1 else 0 end) as cnt_pre_2022
  from tbl_people_evt p
  join tbl_evt e
    on p.id_evt = e.id_evt
 group by p.id_people;

Gives you attended event counts in 2022 and pre-2022 per id_people:

id_people|cnt_2022|cnt_pre_2022|
--------- -------- ------------ 
        3|       1|           1|
        5|       2|           0|
        4|       1|           0|
        6|       0|           1|
        2|       1|           0|
        1|       1|           1|

If you only need id_people,

select p.id_people
  from tbl_people_evt p
  join tbl_evt e
    on p.id_evt = e.id_evt
 group by p.id_people
having sum(case when date_part('year', e.evt_date) = 2022 then 1 else 0 end) = 1
   and sum(case when date_part('year', e.evt_date) < 2022 then 1 else 0 end) = 0;

Result:

id_people|
--------- 
        4|
        2|

CodePudding user response：

You could use the filtered count function as the following:

select P.id_people 
  from tbl_people_evt P join tbl_evt E
  on P.id_evt = E.id_evt
  group by P.id_people
  having count(*) filter (where date_part('year', E.evt_date) = 2022) = 1 -- you may replace 2022 with date_part('year', current_date)
  and count(*) = 1

And if you want to get only the count of people who match the criteria:

with t as
(
  select P.id_people 
  from tbl_people_evt P join tbl_evt E
  on P.id_evt = E.id_evt
  group by P.id_people
  having count(*) filter (where date_part('year', E.evt_date) = 2022) = 1
  and count(*) = 1
)
select count(*) from t;

See demo