SQL Server INNER JOIN and GROUP BY

In sql server I’m trying to group by each sales people some infos as follow:

I have 2 tables: Positions and Clients

In Positions table, I have the following columns: Client_Id, Balance, Acquisition_Cost and in the Clients table I use the following columns: Client_Id and Sales_person.

I want to group by Sales_person (Clients table) the Client_id, Balance, Acquisition_Cost (Positions table)

I tried this:

SELECT Positions.Client_ID, Positions.Balance, Positions.Acquisition_cost
FROM Positions
INNER JOIN Clients ON Positions.Client_ID = Clients.Client_ID
GROUP BY Sales_person

It gives me “Positions.Client_ID is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”.

I precise I’m pretty new on SQL so that does not ring that much a bell to me.

Answer

Thanks all of you guys, here’s the code that works for me in this case:

SELECT Positions.Client_Id, Clients.Sales_person, SUM(Positions.Balance) as sum_balance_impacted, SUM(Positions.Acquisition_cost) as sum_acquisition_cost 
FROM Positions 
INNER JOIN Clients ON Positions.Client_Id= Clients.Client_Id
GROUP BY Clients.Sales_person, Positions.Client_Id```