SQL Query Error while using Group by clause

Nepethya Rana

What i am trying to achieve is that i want result set returned in two groups for two username with latest data

SELECT TOP(100) id , insertdate, modifieddate, detail, username FROM MyTable

WHERE username = 'AAA' or username='1234'

GROUP BY username

ORDER BY insertdate DESC

But when i run this query i get error :

Column 'MyTable.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How do i achieve this?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhishek Sharma9y ago
Accepted answer
Hi,

I am not 100% sure on what you mean when you say "i am trying to achieve is that i want result set returned in two groups for two username with latest data". But to solve the error you are getting you can write the query in below manner
SELECT TOP(100) id , insertdate, modifieddate, detail, username FROM MyTable
WHERE username = 'AAA' or username='1234'
GROUP BY username, id, insertdate, modifieddate, detail
ORDER BY insertdate DESC
0
Abhishek Sharma9y ago
GROUP BY returns a single row for each unique combination of the GROUP BY fields. So in your example, every distinct combination of "username" occurring in rows of "MyTable" results in a row in the query representing the group of rows with the given combination of group by field values.

But as you selected multiple columns "id , insertdate, modifieddate, detail", the SQL server was not able to aggregate them in a unique row based on just the "username" column.

When you group by all the columns that you select, the sql server returns a result beause now it doesn't have to aggregate anything as the columns which are grouped are the columns that are selected.

Please let me know if this helps..!!
0
Nepethya Rana9y ago
Thank you. It would be better if you explain. I would have knowledge of what i am doing with code.
0