SQL Server INNER JOIN and GROUP BY

Question

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.

Does this answer your question? [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Filburt, Jun 23 '20 at 13:49
Sample data and desired results would clarify what you want to do. — Gordon Linoff, Jun 23 '20 at 14:11

dcp · Answer 1 · 2020-06-23T14:13:27.307

For any column in your SELECT that you aren't including in the GROUP BY, you need to use some kind of aggregate function (MAX, SUM, etc.) on the column. So you could write it like this:

SELECT Positions.Client_ID
     , Clients.Sales_person
     , SUM(Positions.Balance) Balance_Sum,
     , SUM(Positions.Acquisition_cost) Acquisition_Cost_Sum
  FROM Positions
INNER JOIN Clients ON Positions.Client_ID = Clients.Client_ID
GROUP BY Positions.Client_ID
       , Clients.Sales_person

If you only want the totals by Sales_person and not client_ID, you could just remove client_id from the SELECT AND GROUP BY:

SELECT Clients.Sales_person
     , SUM(Positions.Balance) Balance_Sum,
     , SUM(Positions.Acquisition_cost) Acquisition_Cost_Sum
  FROM Positions
INNER JOIN Clients ON Positions.Client_ID = Clients.Client_ID
GROUP BY Clients.Sales_person

score 0 · Answer 2 · answered Jun 23 '20 at 14:00

0

You need to add Positions.Client_ID in the GROUP BY Clause, since the select expects this column if you really do not want Positions.Client_ID in the select, then no need to add in the GROUP BY Clause

answered Jun 23 '20 at 14:00

Sundaram Subramanian

23
6

score 0 · Answer 3 · answered Jun 23 '20 at 14:17

0

SELECT a.Client_ID, b.Sales_person, SUM(a.Balance) as Balance_Sum,SUM(a.Acquisition_cost) as Acquisition_Cost_Sum FROM Positions as a INNER JOIN Clients as b ON a.Client_ID = b.Client_ID GROUP BY a.Client_ID, b.Sales_person

answered Jun 23 '20 at 14:17

mae

47
5

1

Although this code might solve the problem, a good answer should also explain **what** the code does and **how** it helps. – BDL Jun 23 '20 at 15:32

score 0 · Accepted Answer · edited Jul 08 '20 at 17:43

0

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```

edited Jul 08 '20 at 17:43

GChuf

1,135
1
17
28

answered Jun 23 '20 at 14:19

Pierre_CM

55
1
8

SQL Server INNER JOIN and GROUP BY

4 Answers4