Home » SQL & PL/SQL » SQL & PL/SQL » Select Query to display Aggregate functions for each row
Select Query to display Aggregate functions for each row [message #272387] Thu, 04 October 2007 16:47 Go to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Hi,
I am struggling to come up with a simple Select query to display the aggregate function result alongside the individual rows. I am not sure if it is even possible with simple Select.
Lets take a simple example with Avg() function.

CREATE TABLE temp_table (
	deptId    number,
         salary number
     )

insert into temp_table values (1,4600);
insert into temp_table values (1,5600);
insert into temp_table values (1,6600);
insert into temp_table values (2,4600);
insert into temp_table values (2,5300);
insert into temp_table values (3,4300);
insert into temp_table values (4,8200);



Now I want the result to be displayed as (id, salary, average_salary). In the example, average salary is 5600. So ti should display

1, 4600,5600
1, 5600, 5600
1, 6000, 6600
...

I can run avg( distinct salary) separately but not with the individual rows.
Here is my real problem:

I have a user-defined aggregate function that groups the given column of duplicate rows into comma sepaarated list. In order to avoid calling this function at the runtime, I decided to create a new column to store the value
eg>
Original Table values:
(1,'a')
(1,'b')
(2,'a')
(3,'d')

should be

(1,'a','a,b')
(2,'b', 'b')
(3,'d','d')

Thank you in advance.

Re: Select Query to display Aggregate functions for each row [message #272392 is a reply to message #272387] Thu, 04 October 2007 17:55 Go to previous messageGo to next message
armalock
Messages: 7
Registered: October 2007
Junior Member
I dont know whether this is simple enough for you but the only way I know of doing this is to do nested sql. Its not so bad:

select deptId, salary, avg_salary
from temp_table,
(select avg(salary) as avg_salary from temp_table);

should do it.
Re: Select Query to display Aggregate functions for each row [message #272409 is a reply to message #272387] Thu, 04 October 2007 23:41 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I will start from the end.
I do not see any connection between original and result values (especially how you derived second row and why you have 1 row less).
Creating such column does not seem to me a good idea at all. You totally break the normalization.
Have you ever thought about INSERT/UPDATE statements called on that table?

I do not see any relation to the first query, however it may be achieved using analytics.
SELECT deptid, salary, AVG(salary) OVER () avg_salary
FROM temp_table;
Re: Select Query to display Aggregate functions for each row [message #272458 is a reply to message #272409] Fri, 05 October 2007 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you want to display the average salaries per department:
SELECT deptid, salary, AVG(salary) OVER (Partition by Deptid) avg_salary
FROM temp_table;
Re: Select Query to display Aggregate functions for each row [message #272460 is a reply to message #272458] Fri, 05 October 2007 02:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I really don't understand what you're talking about with this user defined aggregate function, what it does, or why you think you need it to work out averages.
Re: Select Query to display Aggregate functions for each row [message #272578 is a reply to message #272460] Fri, 05 October 2007 10:30 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
With two different examples in my posting, The only thing I succeded was creating a confusion:-)

Basically avg() was just to illustrate the usage of aggregate function in my case. Once I figure out the semantics, I will replace avg() with my user-defined aggregate function.

flyboy,
It was my mistake to omit additional row;
Here is the scenario:
Original Table values:
(1, 'cat', 'a')
(1, 'dog', 'b')
(2, 'hen', 'a')
(3, 'pig', 'd')

should be
(1, 'cat', 'a', 'a,b')
(1, 'dog', 'b', 'a,b')
(2, 'hen', 'a', 'a')
(3, 'pig', 'd', 'd')

With the result of aggregate function( comma separed list) as the column in the table, I am definitely breaking normalization but its OK for datawarehousing applications.
Re: Select Query to display Aggregate functions for each row [message #272581 is a reply to message #272578] Fri, 05 October 2007 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the STRAGG function to fill your new column the same way JRowbottom use the AVG function.

Regards
Michel
Re: Select Query to display Aggregate functions for each row [message #272597 is a reply to message #272581] Fri, 05 October 2007 12:42 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Michel,
I tried the same way as JRowbottom did but couldn't make it work. It works for AVG() though.

Here is my PLSQL function.

CREATE TYPE Namestable IS TABLE OF VARCHAR2(8000);


CREATE OR REPLACE FUNCTION stragg(p_table_in Namestable)
RETURN varchar2 IS l_return_string VARCHAR2(500);
BEGIN
IF p_table_in.count = 1 THEN
l_return_string := p_table_in(1);
ELSE 
	FOR x IN 1..p_table_in.count
          LOOP
             l_return_string:=REPLACE(l_return_string,','||p_table_in(x),'');
	     l_return_string:=REPLACE(l_return_string,p_table_in(x)||',','');
	     l_return_string := l_return_string || p_table_in(x);  
	     IF x <> p_table_in.count THEN
                 l_return_string:=l_return_string||', ';
             END IF;
          END LOOP;
END IF;
     RETURN l_return_string;
END stragg;


Now if I do:
CREATE TABLE temp_table (
	 
         deptid    number,
         empName  VARCHAR(100)
	);

insert into temp_table values (1,'John');
insert into temp_table values (1,'Rick');
insert into temp_table values (2,'Harry');
insert into temp_table values (3,'Peter');
insert into temp_table values (3,'Lina');
insert into temp_table values (4,'Zolma');

select deptid,
   to_string(CAST(COLLECT(TO_CHAR(NVL(empName,'')) ) AS table_of_names))  
from temp_table 
group by deptid


Works Great but because of Group by I dont get the duplicate deptid rows.
When I try calling the aggregated function + OVER (Partition by deptid), I get the error

ORA-00923: FROM keyword not found where expected

[Updated on: Fri, 05 October 2007 12:50] by Moderator

Report message to a moderator

Re: Select Query to display Aggregate functions for each row [message #272598 is a reply to message #272387] Fri, 05 October 2007 12:47 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
there is an article currently on the front page of ORAFAQ that talks about ANALYTIC FUNCTIONS. It will answer your question about how to do a running total like this, and hopefully clue you as to how analytics work.

Good luck, Kevin
Re: Select Query to display Aggregate functions for each row [message #272602 is a reply to message #272597] Fri, 05 October 2007 12:51 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use this stragg function but Tom Kyte one (you'll find on AskTom), it is analytic aware.

Regards
Michel
Previous Topic: XML EXtract problem (NOT urgent. never was never will be)
Next Topic: A way to check if a string is infact a number
Goto Forum:
  


Current Time: Thu Dec 08 16:32:15 CST 2016

Total time taken to generate the page: 0.13266 seconds