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

Postgresql – How to generate this summary from my tables

I have the following two tables:

CREATE TABLE segments(session_id INT, segment_id INT, 
          length DOUBLE PRECISION, movement BOOLEAN);
INSERT INTO segments (session_id, segment_id, length, movement)
VALUES (49,0,0,'f'),(49,1,180851,'t'),(49,2,1.31082,'f'),(49,3,1580.41,'t'),
(49,4,1.0059,'f'),(49,5,4.47451,'t'),(49,6,1.69973,'f'),(55,0,2.76848,'f'),
(55,1,2.60235,'t'),(55,2,3.807,'f'),(55,3,2.78887,'t'),(55,4,8.47029,'f');

CREATE TABLE trips (trip_id INT, session_ids INT[], distance DOUBLE PRECISION);
INSERT INTO trips (trip_id, session_ids, distance)
VALUES (153,'{49}', 183245.960),(155,'{55}',109.035),
      (156,'{59,60}',909.870);

Representing user trips and corresponding trip segments.

In the segments table column movement shows whether the user is moving t; or stopped f. So I want to count the number of stops (not moving segments) for each trip in my database.

Expected results:

trip_id | distance | stop_count
--------+----------+-----------
  153   |183245.96 |    4
  155   | 109.035  |    3

NB: db<>fiddle

Tags:

2

Answers


  1. 0

    I did it in db<>fiffle.

    Still have few questions – you did not mention how is movement defined (is it just f – for stop?)?
    Why distance in trips differ from sum that comes from segment length?

    Login or Signup to reply.
  2. 0

    You can simply use this query for it.
    If you have other questions feel free to comment.

    select
    t.trip_id,
    t.distance,
    count(*)
--  USE THIS IF YOU OPT FOR REMOVING SECOND CONDITION FROM JOIN
--  count(s.movement) filter(where movement = false)
from
    trips t
inner join
    segments s
on  s.session_id = any(t.session_ids)
and movement = false
group by
    t.trip_id,
    t.distance
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search