How to use group by in SQL Server query?

Question

I have problem with group by in SQL Server

I have this simple SQL statement:

select * 
from Factors 
group by moshtari_ID

and I get this error :

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

This is my result without group by :

and this is error with group by command :

Where is my problem ?

Answer 1

In general, once you start GROUPing, every column listed in your SELECT must be either a column in your GROUP or some aggregate thereof. Let's say you have a table like this:

| ID | Name        | City        |
|  1 | Foo bar     | San Jose    |
|  2 | Bar foo     | San Jose    |
|  3 | Baz Foo     | Santa Clara |

If you wanted to get a list of all the cities in your database, and tried:

SELECT * FROM table GROUP BY City

...that would fail, because you're asking for columns (ID and Name) that aren't in the GROUP BY clause. You could instead:

SELECT City, count(City) as Cnt FROM table GROUP BY City

...and that would get you:

| City        | Cnt |
| San Jose    |  2  |
| Santa Clara |  1  |

...but would NOT get you ID or Name. You can do more complicated things with e.g. subselects or self-joins, but basically what you're trying to do isn't possible as-stated. Break down your problem further (what do you want the data to look like?), and go from there.

Good luck!

Answer 2

When you group then you can select only the columns you group by. Other columns need to be aggrgated. This can be done with functions like min(), avg(), count(), ...

Why is this? Because with group by you make multiple records unique. But what about the column not being unique? The DB needs a rule for those on how to display then - aggregation.