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

Postgresql – Get result where exist on date X and not exist on day Y

I have a table where I store some info about events. My table have columns: id, created_at(which is date field), key(which is varchar255), quantity(which is integer) and event(which is varchar255). I’m making query to take all keys which exists on date X (for example 2022-09-05) and NOT exists on date Y (example 2022-09-06). The table has no relation with other tables.

The query that I tried is:

SELECT s.key
FROM stats s
WHERE created_at = '2022-09-05'
AND NOT EXISTS(
    SELECT *
    FROM stats s
    WHERE s.created_at = '2022-09-06'
    )
GROUP BY s.key
;

The problem is this returns me 0 result, but I expect at least 1.

Tags:

2

Answers


  1. 0

    You can try this

    SELECT s.key
FROM stats s
LEFT JOIN (
    SELECT s.key FROM stats s
    WHERE created_at = '2022-09-05'
    ) dayAfter ON s.key = dayAfter.key
WHERE s.created_at = '2022-09-06'
AND dayAfter.key IS NULL
GROUP BY s.key
    Login or Signup to reply.
  2. 0

    You have to check that the key of 2022-09-05 does not appear on 2022-09-06. So the query changes to

    SELECT s.key
FROM stats s
WHERE s.created_at = '2022-09-05' AND NOT EXISTS
 (SELECT FROM stats st WHERE st.key = s.key AND st.created_at = '2022-09-06');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search