postgresql group by and inner join

sql postgresql group-by inner-join
wonderbummer picture wonderbummer · Jul 4, 2014 · Viewed 20.8k times · Source

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

Raphaël Althaus picture Raphaël Althaus · Jul 4, 2014

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;

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to concatenate strings of a string field in a PostgreSQL 'group by' query?

I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table: ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave and I wanted to group by company_…

Read More
GROUP BY + CASE statement

I have a working query that is grouping data by hardware model and a result, but the problem is there are many "results". I have tried to reduce that down to "if result = 0 then keep as 0, else set it to 1". …

Read More