Home » SQL & PL/SQL » SQL & PL/SQL » GROUP BY clause problem (merged 3)
GROUP BY clause problem (merged 3) [message #376940] Fri, 19 December 2008 04:08 Go to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
HI,
I have a following query:

SELECT   col1,
         col2
FROM     a,
         b
GROUP BY col1,
         col2
ORDER BY col1


Now i want to add another column in ORDER BY clause, eg

SELECT   col1,
         col2
FROM     a,
         b
GROUP BY col1,
         col2
ORDER BY col1,
         col3


But it gives an error saying that col3 is not a GROUP by expression.

Actually I don't want to add that column in GROUP BY clause because it changes the grouping.
So is there any way to use the col3 in ORDER BY without effecting the grouping...???
Re: GROUP BY clause problem (merged) [message #376946 is a reply to message #376940] Fri, 19 December 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once you group, you can no more order by a column that is grouped as this column no more exist.

For instance, if I count the number of persons rooms and find there are 10 in first one and 20 in second one, I can't say I want to know the counts order by person eyes color.

Regards
Michel
Re: GROUP BY clause problem (merged) [message #376967 is a reply to message #376940] Fri, 19 December 2008 04:55 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
You mean that if i used col1 in GROUP BY then i can not use it in the ORDER BY???
Any solution to resolve this issue?
Re: GROUP BY clause problem (merged) [message #376971 is a reply to message #376940] Fri, 19 December 2008 05:12 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Any solution to resolve this issue?

Yes, specify exactly, what you want, because the the current requirement is a little ambiguous (politely said).
Imagine following data:
COL1 COL2 COL3
---- ---- ----
   1    1    1
   1    1    8 
   1    2    5
   1    2    6
   1    2    7

Which of COL2 (1 or 2) is "bigger" based on COL3 value and why?
Re: GROUP BY clause problem (merged) [message #376974 is a reply to message #376940] Fri, 19 December 2008 05:20 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
Okay let me explain it:

I have one column, say col1, which I am using in ORDER BY cluase, as

ORDER BY col1

Now i want to put another column in the ORDER BY cluase. It is actually a hiredate of an employee.

ORDER BY col1, hiredate.

Now col1 is already present in GROUP BY cluase and SQL is saying me to put hiredate in the GROUP BY cluase as well, which i don't want to do.

I hope this will give you a good idea of the situaion.
Re: GROUP BY clause problem (merged) [message #376978 is a reply to message #376940] Fri, 19 December 2008 05:31 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
No joins for the above mentioned table!! Using Cartesian join ?

The columns you specify in SELECT must be in GROUP BY..


Regards,
Oli

[Updated on: Fri, 19 December 2008 05:32]

Report message to a moderator

Re: GROUP BY clause problem (merged) [message #376979 is a reply to message #376940] Fri, 19 December 2008 05:33 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
No, there are some joins.
I am not using hiredate in the SELECT statement. Is it still neccessary to put it in the GROUP BY...???
Re: GROUP BY clause problem (merged) [message #376982 is a reply to message #376979] Fri, 19 December 2008 05:41 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
No....
Post what you did. You should let know what you want to achieve.



SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id Order by department_id, job_id;



SELECT   department_id dept_id,  SUM(salary)
FROM     employees
GROUP BY department_id,  Order by department_id, job_id;


Regards,
Oli

[Updated on: Fri, 19 December 2008 05:51]

Report message to a moderator

Re: GROUP BY clause problem (merged) [message #376983 is a reply to message #376940] Fri, 19 December 2008 05:49 Go to previous messageGo to next message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
ok have a look at the original query:

SELECT Initcap(Last_Name)
||' , '
||Initcap(First_Name) NAME
FROM Employee a,
Overtime_List b,
ot_JobTitle c,
ot_dept d,
ot_Shift e
WHERE a.emp_Id = b.uuId
AND b.jt_No = c.jt_No
AND b.dept_No = d.dept_No
AND b.Shift_No = e.Shift_No
GROUP BY Initcap(Last_Name)
||' , '
||Initcap(First_Name),
b.ytd_New
ORDER BY b.ytd_New

Now i want to add hiredate in ORDER BY CLAUSE
Re: GROUP BY clause problem (merged) [message #376985 is a reply to message #376940] Fri, 19 December 2008 05:52 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
We're getting side tracked here.
As Michel and Flyboy already explained you can not order by a column you don't group by. There are NO exceptions to this rule.
Re-read Flyboys post and think carefully about the question he asks at the end.
Re: GROUP BY clause problem (merged) [message #376988 is a reply to message #376982] Fri, 19 December 2008 06:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
SELECT   department_id dept_id,  SUM(salary)
FROM     employees
GROUP BY department_id,  Order by department_id, job_id;

wont work!
Re: GROUP BY clause problem (merged) [message #376993 is a reply to message #376940] Fri, 19 December 2008 06:17 Go to previous message
waqasbhai
Messages: 118
Registered: August 2008
Location: Pakistan
Senior Member
ok thanks all of you for the reply.
I got the point. It wont work the way i thaught. I need to find some other way out.
Previous Topic: Like with decode (merged)
Next Topic: Trigger help required
Goto Forum:
  


Current Time: Mon Dec 05 13:16:09 CST 2016

Total time taken to generate the page: 0.18656 seconds