This content has been marked as final.
Show 7 replies

1. Re: analytic function and aggregate function
Efficientoracle Jan 27, 2011 3:52 AM (in response to 833901)Hi,
Try This. This tells you the basic differences with samples.
http://www.orafaq.com/node/55
http://www.oraclepassport.com/Oracle_Analytic_and_Aggregate_Functions.html
Regards,
Simma..... 
2. Re: analytic function and aggregate function
Hoek Jan 27, 2011 3:53 AM (in response to 833901)You can find answers in the online Oracle documentation:
http://www.oracle.com/pls/db102/homepage (Database version 10.2)
http://www.oracle.com/pls/db112/homepage (Database version 11.2)
Just simply do a quick search and you'll find topics like:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref962 
3. Re: analytic function and aggregate function
varun4dba Jan 27, 2011 4:02 AM (in response to 833901)hi,
Analytic Functions :
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
Aggregate Functions :
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
let me know if you are feeling any problem in understanding.
thanks.
Edited by: varun4dba on Jan 27, 2011 3:32 PM 
4. Re: analytic function and aggregate function
Efficientoracle Jan 27, 2011 4:03 AM (in response to 833901)HI,
aggregates take "many rows, collapse them into one"
analytics "do not, there is no row collapsing"
the two are orthogonal concepts really.
Regards,
Simma...... 
5. Re: analytic function and aggregate function
833901 Jan 27, 2011 5:17 AM (in response to varun4dba)If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what? 
6. Re: analytic function and aggregate function
BluShadow Jan 27, 2011 5:36 AM (in response to 833901)830898 wrote:
You'd be hard pushed to find a problem that would be solved by both directly. Aggregate functions require the data to be grouped together whereas Analytical functions don't, they just calculate the values for each row based on a window of the data (essentially looking at a group of data without actually grouping it in the result).
If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what?
You could take a query with an analytical query and then group the data down afterwards, but then why would you use the analytical function in the first place. 
7. Re: analytic function and aggregate function
Hoek Jan 27, 2011 5:54 AM (in response to 833901)If a particular problem can be solved by both, then which one to prefer. I am speaking in general, if there are two solutions, then what?
Here's a thread on how Tom Kyte looks at it:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:74525921631614