Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Aggregating Data Using Group Functions

In this article, we will learn about aggregating data using group functions and how to identify and use them.

Group functions are applied on a set of rows and are not like the single row functions which are used to manipulate a single row. The set of rows may have all the rows from the table or all the rows from a group which is formed by splitting the table based on some condition. They will output a single result for a group and can be used to obtain summary information about the group. Group functions can also be used to divide the table into different groups of rows and to define searching criteria on those groups.

There are many types of group functions available in Oracle. Some of them are:

  • SUM
  • AVG
  • MAX
  • MIN
  • COUNT
  • STDDEV
  • VARIANCE

The syntax to use Group Functions is like:

SELECT <column, GROUP_FUNCTION(column)>
FROM <table name>
WHERE <Condition>

Here:

  • SELECT Keyword is used to specify the name of columns on which grouping is to be done.
  • GROUP_FUNCTION is used to denote the grouping function used.
  • FROM specifies the name of the table on which grouping is to be done.
  • WHERE can be used to specify a condition on basis of which grouping will be done.

Since Group Functions are not the single row functions, we can describe them with the help of a block diagram.

GroupFunctions

In this many rows participate in the use of Group Functions and a single result for all the rows is obtained.

Suppose we have a table called Author in our database which looks like:

Author

AUTHOR_ID FIRST_NAME LAST_NAME PHONE BOOKS SALARY COMPANY DOB ADDRESS
1 John Doe 999123456 5 24000 ABC 16-Aug-63 California
5 Steve Brown 698296556 5 45000 ABC 29-Nov-70 Boston
7 Ben Stuart 877034486 4 15000 XYZ 11-Mar-68
4 April Berker 573902845 9 78900 ABC 30-Jun-50 London
2 Stefan Richardson 234551738 7 45000 XYZ 24-Oct-60
3 Elena Snow 890376186 5 20987 PQR 02-Feb-80 Austria
6 Mark Wright 905684728 10 100000 PQR 10-Jul-55 Chicago

SUM Function

SUM function can be used to sum all the numeric values in a column. This function can be used only with columns that have number data type or store numeric data. If there are null values present in the column, then this function will ignore those null values and will do the sum of remaining values.

The syntax of the function is:

SUM( <DISTINCT | ALL> Column)

Step 1: In this step we will learn to use the SUM aggregate function.

Listing 1: SQL statement to obtain the sum of salaries of all the authors.

SELECT SUM(Salary)
FROM Author

When we run the above query, then we will get the sum of salaries of all the authors in a column named SUM(Salary).

We can use DISTINCT keyword to obtain the sum of only distinct values in a column.

The output of the following query is as follows:

Output 1:

SUM(SALARY)
328887
Shows the output of listing 1

Figure 1: Shows the output of listing 1

AVG Function

AVG function can be used to obtain the average value of all the numeric values in a column. This function can be used only with columns that have number data type or store numeric data. If there are null values present in the column, then this function will ignore those null values and will do the average of remaining values.

The syntax of the function is:

AVG( <DISTINCT | ALL> Column)

Step 2: In this step we will learn to use the AVG aggregate function.

Listing 2: SQL statement to obtain the average of salaries of all the authors.

SELECT AVG(Salary)
FROM Author

When we run the above query, then we will get the average of salaries of all the authors in a column named AVG(Salary).

We can use DISTINCT keyword to obtain the average of only distinct values in a column.

The output of the following query is as follows:

Output 2:

AVG(SALARY)
46983.85714

Shows the output of listing 2

Figure 2: Shows the output of listing 2

MAX Function

Max function can be used to obtain the maximum value of all the values in a column. This function can be used only with columns that have number data type or character data type or date data type. If there are null values present in the column, then this function will ignore those null values and will give the maximum of remaining values.

The syntax of the function is:

MAX( <DISTINCT | ALL> Column)

Step 3: In this step we will learn to use the MAX aggregate function

Listing 3: SQL statement to obtain the maximum of first name all the authors(when viewed in alphabetic order).

SELECT MAX(First_Name)
FROM Author

When we run the above query, then we will get the maximum of first name of all the authors in a column named MAX(First_Name). The maximum is obtained by viewing the names in alphabetical order.

We can use DISTINCT keyword to obtain the maximum of only distinct values in a column.

The output of the following query is as follows:

Output 3:

MAX(FIRST_NAME)
Steve

Shows the output of listing 3

Figure 3: Shows the output of listing 3

MIN Function

MIN function can be used to obtain the minimum value of all the values in a column. This function can be used only with columns that have number data type or character data type or date data type. If there are null values present in the column, then this function will ignore those null values and will give the minimum of remaining values.

The syntax of the function is:

MIN( <DISTINCT | ALL> Column)

Step 4: In this step we will learn to use the MIN aggregate function.

Listing 4: SQL statement to obtain the minimum of date of birth all the authors.

SELECT MIN(DOB)
FROM Author

When we run the above query, then we will get the minimum of date of birth of all the authors in a column named MIN(DOB). The minimum is obtained by comparing all the dates and returning the date of birth of the author who is eldest among all.

We can use DISTINCT keyword to obtain the minimum of only distinct values in a column.

The output of the following query is as follows:

Output 4:

MIN(DOB)
30-Jun-50

Shows the output of listing 4
Figure 4: Shows the output of listing 4

COUNT Function

COUNT function can be used to obtain the count the number of rows that will exist for an expression or column in the table. This function can be used with columns of all data types. Expressions can be used in this function also, but they must not evaluate to null. This function will return the number of rows with non-null values of the expression. Expression can also be a name of the column. ‘*’ can be used to count all the rows including those with null values in them.

The syntax of the function is:

COUNT( < * | DISTINCT | ALL> Expression)

Step 5: In this step we will learn to use the COUNT aggregate function.

Listing 5: SQL statement to obtain the count of addresses the authors.

SELECT COUNT(Address)
FROM Author

When we run the above query, then we will get the count of number of rows having non-null address in them.

We can use DISTINCT keyword to obtain the number of rows with distinct address.

The output of the following query is as follows:

Output 5:

COUNT(ADDRESS)
5

 Shows the output of listing 5

Figure 5: Shows the output of listing 5

STDDEV Function

STDDEV function can be used to obtain the standard deviation of the numeric values in column. Standard Deviation is a measure of deviation of values from the mean value. If standard deviation is low then we can assume that the values are closer to the mean. This function can be used with columns of number data types. If there are null values present in the column, then this function will ignore those null values and will give the standard deviation of remaining values.

The syntax of the function is:

STDDEV( < DISTINCT | ALL> Column)

Step 6: In this step we will learn to use the STDDEV aggregate function.

Listing 6: SQL statement to obtain the standard deviation of books written by the authors.

SELECT STDDEV(Books)
FROM Author

When we run the above query, then we will get the standard deviation of books written by authors in a column named ‘STDDEV(Books)’.

We can use DISTINCT keyword to obtain the standard deviation of books with distinct values only.

The output of the following query is as follows:

Output 6:

STDDEV(BOOKS)
2.299068134
Shows the output of listing 6

Figure 6: Shows the output of listing 6

VARIANCE Function

VARIANCE function can be used to obtain the variance of the numeric values in column. Variance is a measure variability or difference in the values of a set. This function can be used with columns of number data types. If there are null values present in the column, then this function will ignore those null values and will give the variance of remaining values.

The syntax of the function is:

VARIANCE( < DISTINCT | ALL> Column)

Step 7: In this step we will learn to use the VARIANCE aggregate function.

Listing 7: SQL statement to obtain the variance of salaries earned by the authors.

SELECT VARIANCE(Salary)
FROM Author	

When we run the above query, then we will get the variance of salaries earned by authors in a column named ‘VARIANCE(Salary)’.

We can use DISTINCT keyword to obtain the variance of salary with distinct values only.

The output of the following query is as follows:

Output 7:

VARIANCE(SALARY)
1010714057
 
Shows the output of listing 7

Figure 7: Shows the output of listing 7

Group Functions With DISTINCT Keyword

We can use all group functions with DISTINCT keyword which is used to take into account only those rows which are having distinct values for a column. Duplicate valued rows will be neglected.

Step 8: In this step we will learn to use the group functions with DISTINCT keyword.

Listing 8: SQL statement to obtain the COUNT of distinct companies for which authors are working.

SELECT COUNT(DISTINCT Company) AS Companies
FROM Author

When we run the above query, then we will get the count of distinct companies for which authors are working in a column named ‘Companies’. Note that we have used ‘AS’ keyword to rename the default column name which we would have obtained in result.

The output of the following query is as follows:

Output 8:

COMPANIES
3

Shows the output of listing 8<

Figure 8: Shows the output of listing 8

Conclusion:

In this article, we have learnt about the aggregate group functions. We have learned how we can use them to obtain the various aggregate results like sum, maximum, minimum, average, count, standard deviation and variance of values.

See you next time!



I have done my bachelor in Computer Science and I am well versed with programming languages such as JAVA, C#, html and done courses on Android development.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login