Oracle COVAR_SAMP FunctionIn this article, I’ll explain what the Oracle COVAR_SAMP function does and show you some examples.

Purpose of the Oracle COVAR_SAMP Function

This function returns the covariance of a set of number pairs.
What does that mean? Here’s an explanation of what a covariance is.
This function, like the CORR and COVAR_POP function, can be run as an aggregate function or as an analytic function.

Syntax – Aggregate Function

When using COVAR_SAMP as an aggregate function, the syntax is:

COVAR_SAMP (expression1, expression2)

Parameters – Aggregate Function

The parameters of the COVAR_SAMP function are:

  • expression1 (mandatory): The first set of numbers to use in the calculation.
  • expression2 (mandatory): The second set of numbers to use in the calculation.
These two parameters are a numeric data type, and return the same data type as the parameters.
 

Examples – Aggregate Function

Here’s an example of the COVAR_SAMP function.
SELECT COVAR_SAMP(fees_paid, fees_required) AS cvsamp
FROM student;

Result:

CVSAMP
7669.551282
 

Syntax – Analytic Function

If you want to use the COVAR_SAMP function as an analytic function, the syntax is:

COVAR_SAMP (expression1, expression2) OVER (analytic_clause)

The only difference here is the addition of the analytic_clause, which is quite common for analytic queries.

 

Parameters – Analytic Function

The parameters of the COVAR_SAMP analytic function are:
  • expression1 (mandatory): The first set of numbers to use in the calculation.
  • expression2 (mandatory): The second set of numbers to use in the calculation.
  • analytic_clause (mandatory): This defines how the analytic function operates. It includes a query partition clause, order by clause, and a windowing clause.

 

Examples – Analytic Function

Here’s an example of using the COVAR_SAMP as an analytic function.
SELECT first_name,
last_name,
COVAR_SAMP(fees_paid, fees_required) OVER (ORDER BY first_name) AS cvsamp
FROM student;
Result:
FIRST_NAME LAST_NAME CVSAMP
Andrew Cooper
Jarrad Winston 5000
John Smith -33916.66667
John Rogers -33916.66667
Julie Armstrong 8500
Mark Anderson -9046.428571
Mark Holloway -9046.428571
Mary Taylor -7209.821429
Michelle Randall -7209.821429
Robert Pickering 859.7222222
Steven Webber 6752.222222
Susan Johnson 8001.363636
Tanya Hall 8731.818182
Tom Capper 7669.551282

 

Similar Functions

Some functions which are similar to the COVAR_SAMP function are:

  • COVAR_POP – returns the population covariance of a set of number pairs.
  • CORR – returns the coefficient of correlation of a set of number pairs.
  • VAR_SAMP – returns the sample variance of a set of numbers
  • VAR_POP – returns the population variance of a set of numbers

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

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!

Improve Your Oracle SQL With My 10-Day Email Course

x