CODEWITHSUNDEEP
sql-server
saman
saman

Reputation: 305

How to use group by in SQL Server query?

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 :

enter image description here

and this is error with group by command :

enter image description here

Where is my problem ?

Upvotes: 7

Views: 40033

Answers (5)

NIKHIL VS
NIKHIL VS

Reputation: 1

If you are applying group clause then you can only use group columns and aggregate function in select

syntax:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Upvotes: -1

Andy Lai
Andy Lai

Reputation: 1894

Try it,

select * 
from Factorys
Group by ID, date, time, factorNo, trackingNo, totalAmount, createAt, updateAt, bark_ID, moshtari_ID

Upvotes: -1

rUCHIt
rUCHIt

Reputation: 63

You need to apply aggregate function such as max(), avg() , count() in group by.

For example this query will sum totalAmount for all moshtari_ID 

select moshtari_ID,sum (totalAmount) from Factors group by moshtari_ID;

output will be

moshtari_ID    SUM
2              120000 
1              200000

Upvotes: 0

BJ Black
BJ Black

Reputation: 2521

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!

Upvotes: 20

juergen d
juergen d

Reputation: 204766

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.

Upvotes: 2

Related Questions