postgresql how to sum all fields where car id exists

March 29, 2023

I have two tables. cars and booking.

I have in booking 5 columns:

km int
km_50 int
km_100 int 
km_200 int 
km_300 int 
km_500 int

So when I have 20 rows then I want to sum all 20 rows with all the datas in booking
is that possible ?

I get a result its also fine but can I have only one row and all added rows is in there ?

now I get this:

    { car_id: 8, k: '0', k1: '2', k2: '0', k3: '0', k4: '0', k5: '0' },
    { car_id: 8, k: '0', k1: '3', k2: '1', k3: '0', k4: '0', k5: '0' },

 SELECT

 b.car_id,

 SUM(be.km) as k,
 SUM(be.km_50) as k1,
 SUM(be.km_100) as k2,
 SUM(be.km_200) as k3,
 SUM(be.km_300) as k4,
 SUM(be.km_500) as k5

 FROM booking b

 INNER JOIN booking_extras be
 ON be.booking_id = b.id

 WHERE car_id = 8

 GROUP BY b.car_id, be.km, be.km_50, be.km_100,
  be.km_200, be.km_300, be.km_500

I want this result:

{ k: '0', k1: '5', k2: '1', k3: '0', k4: '0', k5: '0' }

so when you look at the above result all same key values are added in one.
when a in a row exists k1 = 3 and in ohter row k1 = 5 then I want sum all that in one.

>Solution :

You have the right idea, but you’re grouping by all the columns. Instead, you should group only by the columns that should be unique in the result (the car_id in this case) and aggregate the others (as you’ve done with the sum calls):

SELECT     b.car_id,
           SUM(be.km) as k,
           SUM(be.km_50) as k1,
           SUM(be.km_100) as k2,
           SUM(be.km_200) as k3,
           SUM(be.km_300) as k4,
           SUM(be.km_500) as k5
FROM       booking b
INNER JOIN booking_extras be ON be.booking_id = b.id
WHERE      car_id = 8
GROUP BY   b.car_id -- Only group by car_id