Why is it impossible to group, sort data in a T-SQL table?

Question

Transact SQL

I'm trying to write a request to display the client's data and the total cost of his purchases, as well as sort or group by last name, but I get an error: "Msg 8120, Level 16, State 1, Line 21
Column 'clients.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
I do in MSSMS.

I tried to search on the Internet, from all the answers I found only that I need to enter all the columns that I display in order by or group by, but what's the point if I need to sort / group only 1 specific column.

select clients.surname as Фамилия ,clients.Name as Имя,patronymic as Отчество,
((furnitures.UnitPrice*orders.UnitCount -(furnitures.UnitPrice*orders.UnitCount/100)*Discount+AssemblyCost+DeliveryCost))
as 'Общая стоимость'
from clients join orders on clients.id = orders.ClientsId
join furnitures on furnitures.id = orders.FurnitureId
Group by clients.Surname;

Answer 1

You can't be sure without seeing the tables and data, but summation is probably implied:

select clients.surname as Фамилия ,clients.Name as Имя,patronymic as Отчество,
sum((furnitures.UnitPrice*orders.UnitCount -(furnitures.UnitPrice*orders.UnitCount/100)*Discount+AssemblyCost+DeliveryCost))
as 'Общая стоимость'
from clients join orders on clients.id = orders.ClientsId
join furnitures on furnitures.id = orders.FurnitureId
Group by clients.Surname, clients.Name, patronymic

Answer 2

Normal SQL doesn't let you shoot yourself in the foot and above.
Therefore, when grouping, all displayed fields must appear either in the grouping condition or in the aggregation.
Well, if there is a client_id - you need to group exclusively by it, and the surname, first name, patronymic derivatives of id - here you have to slightly "deceive" the fool protection:

select
Фамилия = min(clients.surname),
Имя = max(clients.Name),
[Ободран на сумму] = sum(.....) 
from clients 
left join orders on clients.id = orders.ClientsId
group by clients.id
order by min(clients.surname)

but a more human option from the point of view of logic is to first group sales by client_id and then join clients to the resulting one;
however, SQL intelligence is quite enough to design an optimal plan in any case