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

Get total Car sessions SQL – Postgresql

I have a table Transactions with id, type, guest_id, timestamp.

I want to get the total Car sessions for the last two months. (assuming all transactions with type ‘Car’ within the same hour from the same guest constitute 1 session).
I tried

SELECT  
    (
        SELECT COUNT(*)::int
        FROM transactions t
        WHERE t.type = 'Car'
        AND t.timestamp  <= gs + '1 hour'::interval
          AND t.timestamp > gs
        group by t.guest_id
    ) AS count
    FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs        
    ORDER BY gs.date ASC

but I get an error ERROR: more than one row returned by a subquery used as an expression

How do I go about this ?

Tags:

2

Answers


  1. 0

    try in this way

    SELECT  
    (
        SELECT COUNT(*)
        FROM transactions t
        WHERE t.type = 'Car'
        AND t.timestamp  <= gs + '1 hour'::interval
          AND t.timestamp > gs
    ) AS count
    FROM generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs        
    ORDER BY gs.date ASC
    Login or Signup to reply.
  2. 0

    Looks like you want a join of your table and the generated series

        SELECT t.guest_id, gs.dt, COUNT(*) cnt
    FROM transactions t
    JOIN generate_series('2022-11-17 00:00:00'::timestamp, '2023-01-17 23:59:59'::timestamp, '1 hour'::interval) AS gs(dt) 
      ON t.timestamp  <= gs.dt + '1 hour'::interval
        AND t.timestamp > gs.dt
    WHERE t.type = 'Car'
    GROUP BY t.guest_id, gs.dt
    ORDER BY t.guest_id, gs.dt
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search