There are two order and customer bases. I want to get the number of orders for a specific customer

 SELECT o.firstname, c.firstname FROM o.order INNER JOIN c.customer ON c.customer_id=o.customer_id

Duplicates customer how to make that customer would appear once and he had the number of orders?

  • the count is given by the count(*) function, which would be in the context of bush-yarders; we need a group by c.firstname, c.customer_id and of course o.firstname in such a query appears to be useless, because there can be several for a one-hour meter - Mike

1 answer 1

Add a grouping at the end of the query and an aggregation function at the beginning (+ you can slightly simplify ON):

 SELECT c.firstname, COUNT(*) AS num FROM o.order INNER JOIN c.customer USING(customer_id) GROUP BY c.customer_id