Question 90351 in PostgreSQL Question posted in
The official documentation can be found here.

Query return only once time participant in year – Postgresql

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
Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    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
)

  2. 0

    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|
    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
  4. 0

    You can aggregate the data by people ID and use a HAVING clause to only get people whose first entry is in the current year:

    select pe.id_people
from tbl_people_evt pe
join tbl_evt e on e.id_evt = pe.id_evt
group by pe.id_people
having min(extract(year from e.evt_date)) = extract(year from current_date);

    Your description doesn’t match your example. In your description you want people to appear "only once this year", in your example you don’t. Above query is according to your example. If you want to have only one entry in the current year (and provided there can be no future entries), I suggest the following HAVING clause instead:

    having array_agg(distinct extract(year from e.evt_date)) = 
         array[extract(year from current_date)];
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search