29

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!

wonderbummer
  • 427
  • 1
  • 5
  • 11
  • Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – philipxy Mar 27 '19 at 23:24

2 Answers2

32

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;
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
  • 1
    Thank you, that's what I searched for! The [postgres tutorial](http://www.postgresqltutorial.com/postgresql-group-by/) confused me, because they say you have to put the `GROUP BY` clause right after the `FROM` or `WHERE`clause. – wonderbummer Jul 04 '14 at 09:42
  • What if I want to add a WHERE clause in the above query, like `WHERE SUM(payment.amount) > 100` – Criesto Jun 28 '18 at 06:46
  • 2
    @Criesto You should add an HAVING clause if you wanna filter on a grouped field : `HAVING SUM(payment.amount) > 100` (after the group by clause) – Raphaël Althaus Jun 28 '18 at 07:18
  • Isn't `GROUP BY customer.id` enough to get the expected result? – pkaramol Dec 22 '20 at 17:22
  • @pkaramol customer.id is not in the select clause and customer.first_name is. Depending on your DBMS, your solution would raise an error. – Raphaël Althaus Dec 23 '20 at 18:20
16

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
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • There is an error in your inner query, `GROUP BY` should comes after `FROM` right ? – Sushin Pv Mar 27 '19 at 20:01
  • 1
    @SushinPv: Of course, fixed it. Actually Teradata's parser will not complain about wrong order of keywords as long as it starts with Select. – dnoeth Mar 27 '19 at 22:09