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.
2
Answers
Rather than updating, I suggest just using a select report which generates the trip totals you want to see:
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.
I suggest using case when, like this,