Home » SQL & PL/SQL » SQL & PL/SQL » Relating to Aggregate function
Relating to Aggregate function [message #218747] Fri, 09 February 2007 15:32 Go to next message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi ,

I cannot understand how the Oracle database shows the result for a query in which I have selected a column without specifying it in Group By clause. I am using the following version

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

My query is like this

Select
col1, col2,sum(col3), sum(col4)
from
( select column1 as col1,
to_number(column1) as col2,
column3 as col3,
column4 as col4
from table1
order by column1
) a
group by col1;

The same query throws - Not a Group By Expression in
version 10.1.0.2.0.

Is this a version mismatch or some thing else. Can any one let me know


Thanks
Panneer Selvam
Re: Relating to Aggregate function [message #218750 is a reply to message #218747] Fri, 09 February 2007 15:40 Go to previous messageGo to next message
Admin@Lightsql.Com
Messages: 36
Registered: November 2006
Member

I do not understand your first question. But, your query is wrong that throws error. It should be like this.

Select
col1, col2,sum(col3), sum(col4)
from
( select column1 as col1,
to_number(column1) as col2,
column3 as col3,
column4 as col4
from table1
order by column1
) a
group by col1, col2.

When you sum col3, col4, and left col1, col2 - you need to group by col1, col2.

Good Luck

Jay - Programming with Oracle & Visual C++, Author of http://www.lightsql.com
Re: Relating to Aggregate function [message #218754 is a reply to message #218750] Fri, 09 February 2007 16:02 Go to previous message
pselvam76
Messages: 12
Registered: February 2007
Junior Member
Hi ,

I do accept that we should group by Col2. But I cannot understand how oracle gives the result even if group by col2 is not present. Can any one help me out.

Thanks
Panneer Selvam

Admin@Lightsql.Com wrote on Fri, 09 February 2007 16:40
I do not understand your first question. But, your query is wrong that throws error. It should be like this.

Select
col1, col2,sum(col3), sum(col4)
from
( select column1 as col1,
to_number(column1) as col2,
column3 as col3,
column4 as col4
from table1
order by column1
) a
group by col1, col2.

When you sum col3, col4, and left col1, col2 - you need to group by col1, col2.

Good Luck

Jay - Programming with Oracle & Visual C++, Author of http://www.lightsql.com

Previous Topic: unique constraint violated
Next Topic: Finding the latest data
Goto Forum:
  


Current Time: Thu Dec 08 10:26:33 CST 2016

Total time taken to generate the page: 0.08768 seconds