postgresql group by and inner join

First, GROUP BY comes at the end of the query (just before order by or having clauses if you have some).

Then, all fields in the select which are not in an aggregation function must be in the group by clause.

so

SELECT customer.first_name, SUM(payment.amount)FROM customerINNER JOIN paymentON payment.customer_id = customer.customer_idGROUP BY  customer.first_name;

But customers with same first_name will be grouped, which is probably not really what you want.

so rather

SELECT  customer.first_name, SUM(payment.amount)FROM customerINNER JOIN paymentON payment.customer_id = customer.customer_idGROUP BY  customer.first_name, customer.customer_id;

You want to group by the customer_id, but get the first_name?

SELECT customer.first_name, SUM(payment.amount)FROM customerINNER JOIN paymentON payment.customer_id = customer.customer_idGROUP BY customer.customer_id, customer.first_name;

You might also do the aggregation in a Derived Table, then you can get additional columns from customer:

SELECT customer.first_name, SumPaymentFROM customerINNER JOIN  (   SELECT customer_id,          SUM(payment.amount) AS SumPayment   FROM payment   GROUP BY customer_id ) AS paymentON payment.customer_id = customer.customer_id