Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Getting confusion about Groupby and Orderby

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
praneethgokul

New User


Joined: 06 May 2005
Posts: 20
Location: Bangalore

PostPosted: Mon May 16, 2005 10:10 am    Post subject: Getting confusion about Groupby and Orderby
Reply with quote

hi all,

Everytime I am getting confusion about Groupby and Orderby.

Can anybody please clarify me, with an example.


Thanks in advance.

Praneeth
Back to top
View user's profile Send private message

manikant pathak

New User


Joined: 09 May 2005
Posts: 37
Location: bangalore

PostPosted: Mon May 16, 2005 10:57 am    Post subject:
Reply with quote

hi,

the diff between group by and order by can be better understood by the following eg:

query 1: select * from table-name
group by column-name;

query 2: select * from table-name
order by column-name;

suppose the column-name in the above eg is dept ......then in the first query all the employee belonging to same department will be displayed together with dept name being displayed once.

but in the latter case the default order is ASC(ascending)...and the result set will be the details of the employee with dept in ascending order and if more than one employees r with the same dept that dept will be repeated for each employee.



i hope this will clarify ur confusion....
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon May 16, 2005 12:26 pm    Post subject:
Reply with quote

Hi Praneeth,

ORDER BY: It is used to arrange the rows in an Ascending or descending order of a column.

SELECT * FROM EMP ORDER BY SAL;
(Will display the rows in ascending order of salary)
SELECT * FROM EMP ORDER BY SAL;

GROUP BY: This is used to group a set of rows then apply a group function like MAX,MIN,AVG,SUM on it. It gives group wise details.

SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO;
(displays dept wise number of employees)


Thanks,
Reddy.
Back to top
View user's profile Send private message
notonly4u

New User


Joined: 26 Apr 2005
Posts: 87
Location: Hyderabad

PostPosted: Mon May 16, 2005 3:46 pm    Post subject:
Reply with quote

One more thing to add....

If we are using Group by we can't use Where condition but we can use Having clause.

Eg: select * from emp group by dept having sal > 2000;

If Iam wrong please make a note.

Regards
Tanden
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Mon May 16, 2005 5:05 pm    Post subject:
Reply with quote

Hi Tanden,

Sorry! We can use WHERE and HAVING together. I want to clear it to you. WHERE clause will check condition for each row but HAVING will check condition for a group of rows.

WHERE & GROUP BY:

SELECT DEPTNO,COUNT(*) FROM EMP WHERE SAL>1000 GROUP BY DEPTNO;
(displays dept wise number of employees whose salary is greater than 1000)

HAVING: Group by is used to sub group the rows based on same column values like DEPTNO wise group. Then in that group we need to select a group based on condition in such a case we need to use HAVING.

HAVING is used to check conditions with GROUP functions like MAX,MIN,SUM etc...

SELECT DEPTNO,COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*) >=3;
(It lists dept wise number of employees if the dept has atleast 3 employees)

I Think now its clear to you. If you have more ques please get back to me.

Thanks,
Reddy.
Back to top
View user's profile Send private message
praneethgokul

New User


Joined: 06 May 2005
Posts: 20
Location: Bangalore

PostPosted: Tue May 17, 2005 6:51 pm    Post subject:
Reply with quote

ThanQ manikanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Confusion b/w index and subscript Deepak kumar25 Mainframe Interview Questions 7 Thu Aug 31, 2017 6:50 am
No new posts DFSORT : GroupBy nnkthri DFSORT/ICETOOL 1 Fri Apr 01, 2016 3:22 pm
No new posts Confusion while passing data to a cal... amitc23 COBOL Programming 5 Mon Mar 07, 2016 12:36 pm
No new posts Joinkeys to Groupby and sum up hiravibk DFSORT/ICETOOL 3 Thu Oct 22, 2015 1:55 am
No new posts SuperC comparison confusion Ashish.Srivastava.123 TSO/ISPF 1 Tue Aug 12, 2014 4:21 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us