Search code examples
sqlpostgresqlgroup-byinner-join

postgresql group by and inner join

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!

Solution

  • 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;