Question 114673 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL add more columns in inner join?

I have two tables:

customers
id name order_id
orders
id customer_id date amount

I would like to get the first order for each customer so I used inner join & min on date. however, I’d like to get all order columns as well.

My SQL query is

SELECT 
    customers.id, customers.name, 
    MIN(orders.date) 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY   
    customers.id;

When I try to add more columns from orders table as following:

SELECT 
    customers.id, customers.name, 
    MIN(orders.date), orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
GROUP BY 
    customers.id;

I get an error

ERROR: column "orders.id" must appear in the GROUP BY clause or be used in an aggregate function

Tags:

3

Answers


  1. 0 
    select oo.*,c.*,fo.* from 
(select o.customer_id,min(o.date)order_date 
from orders o
group by o.customer_id)fo
inner join orders oo on oo.customer_id=fo.customer_id and oo.date=fo.order_date
inner join customer c on c.id=oo.customer_id

    I think this will help you.
    In SQL Group by clause, you can only select grouped column and aggregated columns. For your case, you have to add extra join to take whole data from order table using customer_id and minimun order_date

    Login or Signup to reply.
  2. 0

    Use distinct on w/o group by. You may see this SO discussion for details.

    SELECT distinct on (customers.id)
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id
ORDER BY BY 
    customers.id, orders.date;
    Login or Signup to reply.
  3. 0

    Then don’t use min at the main clause.

    min(orders.date) means that you want to get the minimum date for one column.
    You can filter order first before joining

    SELECT 
    customers.id, customers.name, 
    orders.date, orders.id, orders.amount 
FROM 
    customers
INNER JOIN 
    orders ON customers.id = orders.customer_id 
INNER JOIN (
        SELECT customer_id, Min(date) as MinDate 
        FROM orders 
        GROUP BY customer_id
    ) filtered_order 
        ON filtered_order.customer_id = customers.id 
        AND filtered_order.MinDate = orders.date
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search