skip to Main Content

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

3

Answers


  1. 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. 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. 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