Oracle SQL Aggregate FunctionsOracle SQL aggregate functions are very useful, and are some of the most commonly used functions. Learn what Oracle SQL aggregate functions are and what they do in this article.

What Are Oracle SQL Aggregate Functions?

Aggregate functions are functions that allow you to view a single piece of data from multiple pieces of data.

Most functions in Oracle operate on a single row or single record – such as DECODE or LENGTH.

However, aggregate functions are different because you can use them on multiple rows and get a single value as a result.

An example of an aggregate function is SUM. This function allows you to input a range of values, such as those in a column, and get the sum total of those numbers.

If you had a table that looked like this:

STUDENT_IDFIRST_NAMELAST_NAMEFEES_REQUIREDFEES_PAIDENROLMENT_DATEGENDERADDRESS_STATE
1JohnSmith50010001/FEB/2015MNew York
2SusanJohnson15015012/JAN/2015FColorado
3TomCapper35032006/MAR/2015MNevada
4MarkHolloway50041020/JAN/2015MNew York
5StevenWebber1008009/MAR/2015MNew York
6JulieArmstrong100012/FEB/2015FTexas
7MichelleRandall25023/JAN/2015FFlorida
8AndrewCooper80040004/MAR/2015MTexas
9RobertPickering11010030/JAN/2015MColorado
10TanyaHall15015028/JAN/2015FTexas

You could then use an aggregate function, such as SUM, to get the total of all values in the fees_paid column.

SUM(FEES_PAID)
1710

This function collapses the many rows from the table into a single row that represents the SUM aggregate function.

There are many more aggregate functions, which I’ll explain later in this article.

 

What Is The GROUP BY Clause?

SQL has something called the “group by” clause. What does it mean? What does it do?

The GROUP BY clause allows you to use aggregate functions on parts of your data.

The example above showed the SUM of all of the fees_paid values in the table.

But what if you wanted to see the SUM of fees_paid for each address_state?

Result:

You’ll get this error message. This is because we haven’t grouped our data.

The ORA-00937 occurs whenever you use an aggregate function and don’t include all of the non-aggregate functions from the SELECT clause in a GROUP BY clause;

This is where the GROUP BY clause comes in.

The GROUP BY clause allows you to group your aggregate function by other values.

You’ll need to add GROUP BY, then the names of all of the other fields in the SELECT clause.

Result:

ADDRESS_STATESUM(FEES_PAID)
Texas550
New York590
Colorado250
Nevada320
Florida(null)

This tells Oracle that you want to see the SUM of fees paid, but group by all of the address_state values.

 

What Is The HAVING Clause?

The HAVING clause is another clause that is relevant for aggregate functions.

It allows you to limit the results that are returned based on data after it has been grouped.

What if you wanted to see the SUM of the fees_paid values for each state, but only where the sum was over 500?

You could try use a WHERE clause and say WHERE fees_paid > 500.

Result:

ADDRESS_STATESUM(FEES_PAID)

However, in this case, you’ll get no results.

Why is this?

This is because the WHERE clause is run first, which eliminates rows that don’t meet the criteria. In this case, all rows where fees_paid <= 500 are removed from the result set.

Then, the SUM and GROUP BY are performed. However, there are no rows in the result set to do this to.

How can we get the result we want?

Use the HAVING clause.

The HAVING clause is similar to the WHERE clause, but it works on data after it has been grouped.

This is an important point to remember.

The WHERE clause works on data before it has been grouped, and the HAVING clause works on data after it has been grouped.

The syntax is similar to a WHERE clause.

Result:

I’m getting this error because I’ve specified the fees_paid in the HAVING clause, and I need to specify the aggregate function as well.

So, instead of saying HAVING fees_paid, I need to say HAVING SUM(fees_paid), because that’s the value we are checking.

Result:

ADDRESS_STATESUM(FEES_PAID)
Texas550
New York590

This now shows the result that we want.

 

DISTINCT, UNIQUE, and ALL with Aggregate Functions

Oracle allows you to use several optional keywords within aggregate functions, which are:

  • DISTINCT
  • UNIQUE
  • ALL

DISTINCT and UNIQUE are synonymous with each other so I’ll refer to it as DISTINCT in this article.

They are used in this way:

The ALL keyword is the default, and means that the aggregate function will consider all values when performing its calculation.

The DISTINCT keyword can be specified, and it means the aggregate function will only consider distinct or unique values of the expression in the calculation. It’s also documented on the Oracle website here.

Let’s see an example.

Result:

SUM(FEES_PAID)
1710

This shows the normal SUM function without DISTINCT or ALL.

Result:

SUM(ALLFEES_PAID)
1710

If we add the ALL function, it shows the same as the first example. This is because ALL is the default.

SUM(DISTINCTFEES_PAID)
1460

If we add the DISTINCT keyword, the SUM is smaller, because only distinct values are considered.

 

How Does Oracle Handle NULL Values in Aggregate Functions?

In Oracle, almost all of the aggregate functions ignore NULL values.

The functions that consider NULL values are:

  • COUNT(*)
  • GROUPING
  • GROUPING_ID

If you want to include the records that have NULL values, you can convert them to another value using the NVL function.

What happens if you have NULLs in your data and you use an aggregate function on them?

If you use COUNT and REGR_COUNT, you’ll get either a number for the count of records, or 0.

If you use any other function, and you have only NULL values in the data set, the function will return NULL.

 

Can You Use Nested Aggregate Functions?

Yes, you can.

Let’s say you have the SUM of fees_paid for students in each state, as we mentioned earlier.

Result:

ADDRESS_STATESUM(FEES_PAID)
Texas550
New York590
Colorado250
Nevada320
Florida(null)

What if you wanted to find the average of the SUM of fees paid in each state?

You can wrap the SUM function in another aggregate function, such as AVG.

Result:

AVG(SUM(FEES_PAID)
427.5

The calculation here will find the SUM of fees_paid for each address_state, and then get the AVG of those values.

However, as you might have noticed, you need to remove the field from the SELECT clause. This is because you’re trying to get the overall average, not the average per state.

 

List of Oracle SQL Aggregate Functions

Here’s a list of Oracle SQL aggregate functions, with a short description of what they do.

I have written detailed guides on many functions, and where a guide exists, I’ve linked the function name to it in this table. So, if you click on the function name, you’ll find more information on how to use it and see some examples.

FunctionDefinition
APPROX_COUNT_DISTINCTFinds the approximate number of distinct rows in the expression.
AVGFinds the average of the expression.
COLLECTCreates a nested table from the provided column.
CORRFinds the coefficient of correlation of a set of number pairs.
CORR_KCalculates the Kendall’s tau-b correlation coefficient.
CORR_SCalculates the Spearman’s rho correlation coefficient.
COUNTFinds the number of rows returned by a query.
COVAR_POPFinds the population covariance of a set of number pairs.
COVAR_SAMPFinds the sample covariance of a set of number pairs.
CUME_DISTFinds the cumulative distribution of a value in a group of values.
DENSE_RANKFinds the rank of a row in an ordered group of rows, and rows with the same value get different ranks.
FIRSTUsed with ranking functions to get the first value.
GROUP_IDIdentifies an ID of a grouping level in GROUP BY clauses.
GROUPINGIdentifies superaggregate rows from regular grouped rows.
GROUPING_IDIdentifies the field that has been grouped by.
LASTUsed with ranking functions to get the last value.
LISTAGGOrders data within a set of data and then concatenates it with a specified character.
MAXFinds the maximum value of the expression.
MEDIANFinds the middle value of a set of data.
MINFinds the minimum value of the expression.
PERCENT_RANKFinds the percentage rank of a row, similar to the CUME_DIST function.
PERCENTILE_CONTFinds a value that would fall within the specified range using a specified percentage value and sort method.
PERCENTILE_DISCFinds a value that would fall within the specified range using a specified percentage value and sort method.
RANKCalculates a rank of a value in a group of values, and rows with the same value get the same rank
REGR_ FunctionsFits an ordinary-least-squares regression line ot a set of number pairs.
STATS_BINOMIAL_TESTTests the difference between a sample proportion and a given proportion.
STATS_CROSSTABUses a method to analyse two variables using crosstab.
STATS_F_TESTChecks whether two values are significantly different.
STATS_KS_TESTChecks whether two samples are from the same population, or different populations with the same distribution.
STATS_MODEFinds the value that occurs the most.
STATS_MW_TESTPerforms a Mann Whitney test on two populations.
STATS_ONE_WAY_ANOVATests the difference in means for groups for statistical significance.
STATS_T_TEST_*Checks the significance of a difference of means.
STATS_WSR_TESTPerforms a Wilcoxon Signed Ranks test to see if the median of the differences is significantly different from 0.
STDDEVFinds the standard deviation of the set of values.
STDDEV_POPFinds the population standard deviation of a set of values.
STDDEV_SAMPFinds the cumulative sample standard deviation of a set of values.
SUMFinds the sum of values.
SYS_OP_ZONE_IDFinds a Zone ID from a specified row ID
SYS_XMLAGGAggregates XML documents and produces a single XML document.
VAR_POPFinds the population variance of a set of numbers.
VAR_SAMPFinds the sample variance of a set of numbers.
VARIANCEFinds the variance of a set of numbers.
XMLAGGAggregates XML documents and produces a single XML document.

 

So, that’s how you use aggregate functions in Oracle SQL. If you have any questions, please leave a comment at the end of this post.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 21-day Oracle Tips email course.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit