Home » SQL & PL/SQL » SQL & PL/SQL » analytical functions
analytical functions [message #409284] Sun, 21 June 2009 23:27 Go to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi All,
Currently my query is like this.

select min(col1),col2,col3 from test
group by col2,col3;


How can i add another column to this query without adding that in group by clause?


like below

select min(col1),col2,col3,col4 from test
group by col2,col3;


is there any way to achieve this by using analytical functions.

thanks in advance.

Hari
Re: analytical functions [message #409285 is a reply to message #409284] Sun, 21 June 2009 23:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
SQL Reference
Chapter 5 Functions
Section Analytic Functions

Section MIN, see exemples.

Regards
Michel
Re: analytical functions [message #409286 is a reply to message #409284] Mon, 22 June 2009 00:04 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

Hi Michel,

Thanks for the reply. I tried the query as per link suggested.

select min(col1),col2,col3,MIN(col4) OVER(PARTITION BY col2 ORDER BY col3
   RANGE UNBOUNDED PRECEDING) from test
   group by col2,col3;



Its giving not a group by expression on col4.

Regards
Hari

Re: analytical functions [message #409288 is a reply to message #409284] Mon, 22 June 2009 00:09 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Its giving not a group by expression on col4.


Are you grouping by Col4 ??

Have you clearly gone through the link suggested

Regards,
Ashoka BL
Re: analytical functions [message #409292 is a reply to message #409284] Mon, 22 June 2009 00:12 Go to previous messageGo to next message
hari.choprala
Messages: 65
Registered: November 2006
Location: Mumbai
Member

hi,

Iam not grouping the result by col4.

My aim is to add col4 in select list but not in group by clause. So i just tried the query by using analytical functions.

regards
Hari
Re: analytical functions [message #409293 is a reply to message #409284] Mon, 22 June 2009 00:15 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Quote:

My aim is to add col4 in select list but not in group by clause. So i just tried the query by using analytical functions.


What is happening then ? are you getting any errors ? Post DDL and DML

[Updated on: Mon, 22 June 2009 02:06] by Moderator

Report message to a moderator

Re: analytical functions [message #409310 is a reply to message #409292] Mon, 22 June 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to add col4, why do you use min(col4)?

Your requirements are not clear.
Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: analytical functions [message #409781 is a reply to message #409310] Wed, 24 June 2009 00:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi

SQL>select deptno, sum(salary)
  2  from emp
  3  group by deptno;

    DEPTNO|SUM(SALARY)
==========|===========
         1|      31200
         2|      11300
         3|       5000

3 rows selected.

SQL>select deptno, dname, sum(salary)
  2  from emp
  3  group by deptno;
select deptno, dname, sum(salary)
               *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

SQL>select o.deptno,
  2  (select distinct i.dname from emp i where o.deptno = i.deptno),
  3  sum(o.salary)
  4  from emp o
  5  group by o.deptno;

    DEPTNO|(SELECTDISTINCTI.DNA|SUM(O.SALARY)
==========|====================|=============
         1|IT                  |        31200
         2|MECH                |        11300
         3|ELEC                |         5000

3 rows selected.


regards,
Delna
Re: analytical functions [message #410121 is a reply to message #409284] Thu, 25 June 2009 06:52 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Delna, do you have a different version of the emp table? Is this the built-in from scott/tiger? Your example did not work on my db and I don't think I changed it on this db.

Also I'd probably just do this as a regular join.
Re: analytical functions [message #410131 is a reply to message #409781] Thu, 25 June 2009 07:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This will work (changing the inline select from querying EMP to DEPT:
select o.deptno,
      (select distinct i.dname from scott.dept i where o.deptno = i.deptno),
      sum(o.sal)
from emp o
group by o.deptno;
but you cannot have an inline select returning more than one row.

The OP needs to adress this test case, and show us what results he expexts to get back - once we know what results he's looking for, we can produce a query to show them:
create table test_231 (col_1 number, col_2 number, col_3 number,col_4);

insert into test_231 values (1,1,1,5);
insert into test_231 values (2,1,1,6);
insert into test_231 values (3,1,1,7);
insert into test_231 values (4,1,1,8);
insert into test_231 values (1,2,1,5);
insert into test_231 values (2,2,1,6);
insert into test_231 values (3,2,1,7);
Re: analytical functions [message #410471 is a reply to message #410121] Sat, 27 June 2009 03:43 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi smartin,

Here emp is user created table.
Not in SCOTT schema.

SQL>select banner from v$version;

BANNER
================================================================================
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

Elapsed: 00:00:00.43
SQL>desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(38)
 DNAME                                              VARCHAR2(20)
 EMPNO                                              NUMBER(38)
 ENAME                                              VARCHAR2(20)
 SALARY                                             NUMBER(9,2)


By the way, is there any error you are facing?
Please explain.

regards,
Delna
Previous Topic: help with inline view query
Next Topic: looping error
Goto Forum:
  


Current Time: Sat Dec 03 16:09:45 CST 2016

Total time taken to generate the page: 0.13872 seconds