Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

GROUP BY with multiple columns.

877476Aug 26 2011 — edited Aug 26 2011
I have little query about GROUP BY clause.

Sometimes we will give multiple column names in GROUP BY .If it behaves in the same way as ORDER BY (multiple columns) i would love to have an example where I can use GROUP BY multiple columns. Anyways howsoever it behaves it will be really appreciated if anyone can explain a scenario where GROUP BY with multiple columns can be used.

I know about the usage of aggregate functions with GROUP BY but using only one column.

Thanks..!!!
This post has been answered by 842382 on Aug 26 2011
Jump to Answer

Comments

Jhon
i think u want like this:

select max(comm),avg(sal) from
emp
group by comm,sal
order by comm,sal
842382
Hi,

Group by is used for grouping of same records. check this bellow scenario

SELECT registrationid, dateofbirth, age FROM prawin62 WHERE registrationid = 22;
22	1/23/1975	54
22	5/18/2011	66
22	5/18/2011	66
22	5/18/2011	66
22	5/18/2011	66
22	5/18/2011	66
SELECT registrationid, sum(age) FROM prawin62 WHERE registrationid = 22 GROUP BY registrationid;
22	384
SELECT registrationid, dateofbirth, sum(age) FROM prawin62 WHERE registrationid = 22 GROUP BY registrationid, dateofbirth ;
22	5/18/2011	330
22	1/23/1975	54
Thanks,
Praveen
877476
Thanks praveen...
By your example can i conclude that GROUP BY multiple columns will form groups by first column... then by second column and so on.. and nested groups will be formed in the order the columns are mentioned from left to right in GROUP BY expression.
William Robertson
874473 wrote:
Thanks praveen...
By your example can i conclude that GROUP BY multiple columns will form groups by first column... then by second column and so on.. and nested groups will be formed in the order the columns are mentioned from left to right in GROUP BY expression.
Not sure how you conclude that. A group is simply produced for each distinct combination of the specified keys. There is no nesting or left to right ordering etc.
842382
Answer
No not like that

I think Group by doesn't follow any order ..

If we execute like this

SELECT registrationid, dateofbirth, sum(age) FROM prawin62 WHERE registrationid = 22 GROUP BY dateofbirth ,registrationid;
22	1/23/1975	54
22	5/18/2011	330
it will give same result based on the dateof birth ...


~Praveen
Marked as Answer by 877476 · Sep 27 2020
877476
Thanks very much praveen...
I was thinking that GROUP BY also works in same way as ORDER BY...
877476
Thanks william...
with your explanation i am crystal clear about GROUP BY now
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 23 2011
Added on Aug 26 2011
7 comments
33,515 views