CODEWITHSUNDEEP
sqlgroup-by
Hunsu
Hunsu

Reputation: 3381

Why we use group by in sql

When we make a SQL request like this:

SELECT attr1, attr2, AGGR(*)
  FROM SomeTable
 GROUP BY attr1, attr2

for some aggregate function, we have to include a GROUP BY clause and list attr1 and attr2 in it. My question is: why doesn't the SQL DBMS do it itself?

Upvotes: 3

Views: 2558

Answers (4)

user72380
user72380

Reputation: 17

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Upvotes: 0

pushplamit
pushplamit

Reputation: 1

Because there are two functions type in sql - Single function & Group function . If you want to access the group data then you have to call 'Group by' function type .

For Example, Suppose you have a table of any organization with the column 'deptno' . Now if you want to count the 'no of employee' in each dept. then call count(*) ....group by deptno. The result will show the no of employee in each group .

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881103

Because you may want to group by attr1, attr2 and attr3, even though you have no interest in the actual attr3 value itself.

In other words, you may be required to group by the non-aggegated columns but this in only at a minimum. There's nothing preventing you from grouping by columns not actually included in the query.

And, since the query evaluator doesn't know what you wanted (or it wants you to follow the relevant standard), it may think it safer to insist you explicitly state it.

Upvotes: 3

xdazz
xdazz

Reputation: 160833

You could do it with out GROUP BY, then you get the average from the whole table. With GROUP BY, you get the average for each group.

Upvotes: 0

Related Questions