Invalid left join on SQL Server

Question

I´m trying to excecute a simple left join on SQL Server but it keeps getting me the same message.

Select * from customers left join orders on customers.id = orders.customer_id group by customers.id order by amount;

Msg 8120, Level 16, State 1, Line 39 Column 'customers.first_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I´m not sure what else to do. If it helps, here there are my tables:

CREATE TABLE customers(id INT IDENTITY(1,1) PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100));

CREATE TABLE orders(id INT IDENTITY(1,1) PRIMARY KEY, order_date DATE, amount DECIMAL(8,2), customer_id INT, FOREIGN KEY(customer_id) REFERENCES customers(id));

Please add sample data and desired results. If you're grouping by `customers.id` you need that in the select list as well as an aggregate. What is it that you're looking for in the results? — squillman, Aug 25 '21 at 22:13
The `GROUP BY` clause has to include all columns in the `SELECT` clause that are not selected via aggregate functions (e.g. `MAX`, `MIN`, `COUNT`). Try using something other than `SELECT *` — e_i_pi, Aug 25 '21 at 22:14
I must say, the error message seems to be pretty clear, what about it are you having difficulty with? — Charlieface, Aug 25 '21 at 22:28
Simply search for "first in group". It has nothing to do with any join and is just a matter of misunderstanding how the GROUP BY clause works. — SMor, Aug 25 '21 at 23:19

score 0 · Answer 1 · answered Aug 26 '21 at 00:54

Once you create an aggregate using GROUP BY, you can only SELECT what you have grouped by and the aggregate functions (e.g., MAX, MIN, SUM, COUNT, etc.). For your query:

SELECT * 
FROM customers 
LEFT JOIN orders on customers.id = orders.customer_id 
GROUP BY customers.id 
ORDER BY amount;

Since you GROUP BY customers.id, the only things that can appear in your SELECT list is customers.id and aggregate functions. You are getting the error because the * means all columns, but you are only allowed to use customers.id and the aggregate function. For example, this would work:

SELECT customers.id 
FROM customers 
LEFT JOIN orders on customers.id = orders.customer_id 
GROUP BY customers.id 
ORDER BY amount;

As far as what you want to see, I cannot tell from this query. If you wanted to see the smallest order for each customer id, you could do that with:

SELECT customers.id, MIN(orders.amount) AS [SmallestOrder] 
FROM customers 
LEFT JOIN orders on customers.id = orders.customer_id 
GROUP BY customers.id 
ORDER BY amount;

So the cause of the error is hopefully clear now, but what data you want to find isn't.

Invalid left join on SQL Server

1 Answers1