postgresql group by和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:

我想要一个SQL中的查询,它同时执行INNER JOIN和GROUP BY。我尝试了以下不起作用:

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!

先谢谢你!

2 个解决方案

#1

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

首先,GROUP BY出现在查询的末尾(如果你有一些,则在order by或者有子句之前)。

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

然后,select中不在聚合函数中的所有字段必须在group by子句中。

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.

但具有相同first_name的客户将被分组,这可能不是您想要的。

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;

#2

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

您想按customer_id分组,但获取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
   GROUP BY customer_id
   FROM payment
 ) AS payment
ON payment.customer_id = customer.customer_id

#1