postgresql group by 和inner join

postgresql group by and inner join

提问by wonderbummer

I want a query in SQL which does INNER JOINand GROUP BYat 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!

先感谢您！

回答by Rapha?l Althaus

First, GROUP BYcomes at the end of the query (just before order byor havingclauses if you have some).

首先，GROUP BY出现在查询的末尾（就在order byorhaving子句之前，如果有的话）。

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

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

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.

但是具有相同 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;

回答by dnoeth

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
   FROM payment
   GROUP BY customer_id
 ) AS payment
ON payment.customer_id = customer.customer_id