Home » SQL & PL/SQL » SQL & PL/SQL » Displaying all columns in SELECT statement, but group by few (Oracle 10g, Win XP)
Displaying all columns in SELECT statement, but group by few [message #402687] Tue, 12 May 2009 01:40 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have a scenario,

I have a table ( Say eg. SCOTT.EMP), i want to display Empno,Ename,Sal,Job,Hiredate,Deptno columns but need to group by only on Deptno and Job, how do i do it ?

Select Empno,Ename,Sal,Job,Hiredate,Deptno from emp group by Deptno,Job


The above query will obviously give error(ORA-00979: not a GROUP BY expression). Do i need to use Analytical function to achieve it ?

I tried the following, but i don't think this is the feasable solution,

select empno,ename,sal,hiredate,deptno,job from (SELECT empno, ename, sal, hiredate,deptno,job,
       ROW_NUMBER () OVER ( PARTITION by deptno ORDER BY deptno) emp_deptno,
       ROW_NUMBER () OVER (PARTITION BY job ORDER BY job) emp_job
  FROM emp) order by deptno,job


Please note i am not using any Aggregated functions.

Regards,
Ashoka BL
Re: Displaying all columns in SELECT statement, but group by few [message #402688 is a reply to message #402687] Tue, 12 May 2009 01:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What output do you expect if there are multiple empno's per department-job combination?
Group by means that you only display one value per combination.
Re: Displaying all columns in SELECT statement, but group by few [message #402693 is a reply to message #402687] Tue, 12 May 2009 01:51 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Frank,

That means that i should have an Aggregated function rite,

Otherwise..if i group With respect to Deptno,and Job, the employee details(empno,ename..) will be different for Deptno and Job combination.

Let me once again go into my requirement and get back to you

Regards,
Ashoka BL
Re: Displaying all columns in SELECT statement, but group by few [message #402695 is a reply to message #402687] Tue, 12 May 2009 01:57 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Frank,

Assume if i want only one record( may be top-1) for the Deptno,Job Combination, Do i need to use analytical function for it like following ??

  SELECT empno, ename, sal, hiredate,deptno,job,
       ROW_NUMBER () OVER ( PARTITION by deptno ORDER BY deptno) emp_deptno,
       ROW_NUMBER () OVER (PARTITION BY job ORDER BY job) emp_job
  FROM emp


Re: Displaying all columns in SELECT statement, but group by few [message #402710 is a reply to message #402695] Tue, 12 May 2009 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Assume if i want only one record( may be top-1) for the Deptno,Job Combination

The top-1 for which criteria?

Quote:
Do i need to use analytical function for it like following ??
ROW_NUMBER () OVER ( PARTITION by deptno ORDER BY deptno

This one gives an undeterministic result.

Regards
Michel

Re: Displaying all columns in SELECT statement, but group by few [message #402728 is a reply to message #402687] Tue, 12 May 2009 03:54 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Michel,

That's true....i tested the query which i posted, it is giving undesired results.

Thanks for the reply.
Re: Displaying all columns in SELECT statement, but group by few [message #402731 is a reply to message #402728] Tue, 12 May 2009 04:12 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ashoka_bl,

Can you post some examples which will show us what is the desired and undesired results? This might help others to understand your requirement better (I think).

Regards,
Jo
Previous Topic: procedure output parameter with precision
Next Topic: XMLTYPE in Oracle
Goto Forum:
  


Current Time: Sat Dec 03 22:16:29 CST 2016

Total time taken to generate the page: 0.09462 seconds