Getting confusion about Groupby and Orderby

praneethgokul · New User Joined: 06 May 2005 Posts: 20 Location: Bangalore

hi all,

Everytime I am getting confusion about Groupby and Orderby.

Can anybody please clarify me, with an example.

Thanks in advance.

Praneeth

manikant pathak · New User Joined: 09 May 2005 Posts: 37 Location: bangalore

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....

ovreddy · Posted: Mon May 16, 2005 12:26 pm

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.

notonly4u · New User Joined: 26 Apr 2005 Posts: 87 Location: Hyderabad

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

ovreddy · Posted: Mon May 16, 2005 5:05 pm

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.

praneethgokul · New User Joined: 06 May 2005 Posts: 20 Location: Bangalore

ThanQ manikanth