Analytic Functions vs. Aggregate Functions: Examples in SQL (Basic 1 - 10)

Analytic functions and aggregate functions are two important types of SQL functions that can be used to analyze and summarize data. However, there are some key differences between the two types of functions.

Analytic functions operate on a group of rows, called a window, and return multiple rows for each group. The window is defined by the Analytic_clause, which specifies the range of rows used to perform the calculations. Analytic functions can be used to compute cumulative, moving, centered, and reporting aggregates.

Aggregate functions, on the other hand, return a single result row based on groups of rows. 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. Oracle then applies the aggregate functions to each group of rows and returns a single result row for each group.

Examples

The following examples illustrate the difference between analytic functions and aggregate functions in SQL:

Aggregate function example

The following SQL query uses the aggregate function COUNT() to calculate the total number of employees in each department:

SQL

SELECT DEPT_NO, COUNT(*) AS EMPLOYEE_COUNT
FROM EMP
GROUP BY DEPT_NO;

This query will return the following results:

DEPT_NO EMPLOYEE_COUNT
---------- -------------
10             3
20             4
30             4
40             2

Analytic function example

The following SQL query uses the analytic function COUNT() to calculate the cumulative number of employees in each department:

SQL

SELECT ID, NAME, DEPT_NO, COUNT(*) OVER (PARTITION BY DEPT_NO ORDER BY ID) AS CUMULATIVE_EMPLOYEE_COUNT
FROM EMP;

This query will return the following results:

ID NAME DEPT_NO CUMULATIVE_EMPLOYEE_COUNT
---------- ---------- ---------- -------------
101 Ravi               10             1
104 Manu               10             2
110 Aman               10             3
106 Subho              20             1
107 Akhil              20             2
109 Shiva              20             3
111 Nik                20             4
102 Rajan              30             1
103 Pritesh            30             2
105 Gaurav             30             3
113 Shaan              30             4
108 Neeraj             40             1
112 Sri                40             2

Conclusion

Analytic functions and aggregate functions are both powerful tools for analyzing and summarizing data. However, they have different strengths and weaknesses. Analytic functions are generally faster and more efficient, but they can be more complex to use. Aggregate functions are simpler to use, but they may be slower and less efficient.

The best type of function to use for a given task will depend on the specific needs of the task. If you need to calculate cumulative, moving, centered, or reporting aggregates, then you should use an analytic function. If you need to calculate summary aggregates, then you can use either an analytic function or an aggregate function.