postgresql group by and inner join

Question

I want a query in SQL which does INNER JOIN and GROUP BY at the same time. I tried the following which doesn't work:

SELECT customer.first_name, SUM(payment.amount) FROM customer GROUP BY customer.customer_id INNER JOIN payment ON payment.customer_id = customer.customer_id; 

Thank you in advance!

Answer 1

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 customer INNER JOIN payment ON payment.customer_id = customer.customer_id GROUP 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 customer INNER JOIN payment ON payment.customer_id = customer.customer_id GROUP BY  customer.first_name, customer.customer_id; 

Answer 2

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

SELECT customer.first_name, SUM(payment.amount) FROM customer INNER JOIN payment ON payment.customer_id = customer.customer_id GROUP 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, SumPayment FROM customer INNER JOIN   (    SELECT customer_id,           SUM(payment.amount) AS SumPayment    FROM payment    GROUP BY customer_id  ) AS payment ON payment.customer_id = customer.customer_id