Home Msg 8120 – Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Questions

Msg 8120 – Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

byMR

October 2, 2022

select sum(orderinfo.orderamount) as total, userinfo.username
from orderinfo
left join userinfo on orderinfo.userid = userinfo.userid
group by orderinfo.userid
order by total desc

Table orderinfo:
userid
orderamount

table userinfo:
userid
username

I will need to search for top 10 most buy users with their names.
But I got this error:

Msg 8120, Level 16, State 1, Line 1
Column ‘userinfo.username’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I am using SSMS
I must be doing something wrong…please help

Thanks

>Solution :

You cannot aggregate by the userid and also select the username. The quickest fix here would be to aggregate by both the userid and username:

SELECT SUM(oi.orderamount) AS total, ui.username
FROM orderinfo oi
LEFT JOIN userinfo ui ON oi.userid = ui.userid
GROUP BY ui.userid, ui.username
ORDER BY total DESC;