[SQL] HOW TO Update column table

I am trying to create a sql query to update my a column in the table as shown below:

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

OUTPUT should look like:

ID | TripTotal

1 | 3

2 | 2

3 | 1

4 | 2

5 | 0

so when there is no trip record it should return 0

I tried using UPDATE driver SET totaltripmade = (SELECT Count(Lnum) from TRIP);

I dont know how to use group by to calculate for distinct count. The result works to show the count for all trips not by user.

Continue reading...