2
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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lila C
  • 39
  • 3
  • 1
    Does this answer your question? [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Christian Oct 02 '22 at 02:11
  • 1
    https://www.google.com/search?q=Column+is+invalid+in+the+select+list+because+it+is+not+contained+in+either+an+aggregate+function+or+the+GROUP+BY+clause: "As we know that “group by” return single row, so we need to apply an aggregate function to columns not used in group by clause to avoid this error." – Christian Oct 02 '22 at 02:13
  • 2
    Add username to the group by. In an aggregation query, a column is either an aggregation or included in the group by – John Cappelletti Oct 02 '22 at 02:13

1 Answers1

2

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360