Sql select group by error: field not contained in aggregate function or group by clause

Every field in your select statement that you are not performing an aggregate function on (Sum, Avg, Max, etc) must be listed in the Group By clause. Also, the Group By does not allow field aliases, you should use the actual field name there.

If you get your example working with the sub-query, all you need to do is reference the 'my' alias for your user in the outside query, then remember to group on it.

The other posters are picking up on your errors on the group by, I'm going to attempt to answer the underlying question.

I think what you are trying to do is just return the highest ranking UserName based on total sales by Product Type. The easiest way to do that is to use the ROW_NUMBER function with PARTITION BY - see ROW_NUMBER (Transact-SQL) | Microsoft Docs[^]

For example:

;with CTE AS
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
)
SELECT CTE.* FROM CTE WHERE rn = 1

I've chosen to use a Common Table Expression as I find them easier to follow - the equivalent using a sub-query would be

SELECT * FROM 
(
	SELECT U.Name AS userName, SP.Type AS productType, SUM(SP.Price) AS TotalSales
	, ROW_NUMBER() OVER (PARTITION BY sp.type ORDER BY SUM(SP.Price) DESC) as rn
	FROM #SaleProduct SP
	LEFT JOIN #Sale S ON SP.SaleId = S.SaleId
	LEFT JOIN #User U ON U.UserId = S.UserId
	GROUP BY SP.Type, U.Name
) as a
WHERE rn = 1

Points to note:
- I've used PARTITION BY to restart the numbering within each "group" of things (in this case product types). If I just run the inner query I get these results:

ABC	Desktop	888.00	1
XYZ	Laptop	999.00	1
ABC	Server	777.00	1
XYZ	Server	555.00	2

Notice that rn is showing who came in at number 1, number 2 etc.

This solution does not take into account ties (i.e. two salemen have exactly the same amount of sales for a specific product). Joint winners! To cater for that scenario you would be better off using RANK - see RANK (Transact-SQL) | Microsoft Docs[^]