7

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
saman
  • 305
  • 2
  • 4
  • 17
  • 5
    You need to learn about grouping. Please take a tutorial first – juergen d Aug 01 '16 at 20:35
  • What are you trying to achieve? What output are you trying to get? – Mureinik Aug 01 '16 at 20:36
  • The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. In case,you are trying to achieve any aggregation,then only go with this – Channa Jul 13 '19 at 11:04

5 Answers5

20

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!

BJ Black
  • 2,483
  • 9
  • 15
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.

juergen d
  • 201,996
  • 37
  • 293
  • 362
0

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
rUCHIt
  • 63
  • 1
  • 7
-1

Try it,

select * 
from Factorys
Group by ID, date, time, factorNo, trackingNo, totalAmount, createAt, updateAt, bark_ID, moshtari_ID
gofr1
  • 15,741
  • 11
  • 42
  • 52
Andy Lai
  • 1,714
  • 1
  • 14
  • 17
-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 ]];
Suraj Sukale
  • 1,778
  • 1
  • 12
  • 19
NIKHIL VS
  • 1