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

Postgresql – How to update column table

I am trying to create a SQL query to update a column in my table as shown here.

Driver table:

ID TripTotal
1 NULL
2 NULL
3 NULL
4 NULL

Trip table:

TRIP ID DRIVER ID TRIP
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 3 1
7 4 1
8 4 2

The result set should look like this:

ID TripTotal
1 3
2 2
3 1
4 2
5 0

When there is no trip rows, the result should return 0.

I tried using:

UPDATE driver 
SET totaltripmade = (SELECT COUNT(Lnum) FROM TRIP);

I don’t know how to use GROUP BY to calculate for distinct count. The result works to show the count for all trips not by user.

Tags:

2

Answers


  1. 0

    Rather than updating, I suggest just using a select report which generates the trip totals you want to see:

    SELECT d.id, COALESCE(SUM(t.trip), 0) AS TripTotal
FROM driver d
LEFT JOIN trip t
    ON t.driver_id = d.id
GROUP BY d.id
ORDER BY d.id;

    Note that we use a left join here to ensure that every driver appears in the output, even if there are no corresponding trip records in the second table.

    Login or Signup to reply.
  2. 0

    I suggest using case when, like this,

    UPDATE driver
SET triptotal = (
CASE
WHEN EXISTS (
select 1 from trip
where trip.driver_id = driver.id)
THEN (
select count(*) from trip
where trip.driver_id = driver.id)
ELSE 0
END
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search