Overview

Introduction

Reporting Aggregated Data Using the Group Functions

In this module, you will explore the concepts of using the group functions in relational tables using the SELECT statement.

  • Identify Available Group Functions
  • Using Group functions
  • Group Data using GROUP BY Clause
  • Include or Exclude Grouped Rows

Exercise 1 - Identify Available Group Functions

Group Functions act against one or more rows and return a single result. Group Functions are also known as Aggregate Functions. Aggregate Function performs a variety of actions such as counting all the rows in the tables, averaging a column’s data, and summing numeric data. Aggregate Functions also searches the table to find the MAX and MIN values in a column. Below are some of the group functions that are described in the tasks.

In this exercise, you will learn the following about the group functions:

  • Using Aggregate Function - AVG
  • Using Aggregate Function - COUNT
  • Using Aggregate Function - MEDIAN
  • Using Aggregate Function - MIN
  • Using Aggregate Function - MAX
  • Using Aggregate Function - SUM

Exercise 2 - Using Group Functions

Aggregate Functions are intended to group together multiple rows based on supplied common factor and return a single result for the entire group rather than one result for each row in the table. Aggregate Functions are most commonly used along with GROUP BY clause in a SELECT statement. These functions can appear in SELECT lists and in ORDER BY and HAVING clauses. Aggregate Functions are not allowed in a WHERE clause.

In this exercise you will learn the following about the group functions:

  • Group Function using GROUP BY clause
  • Group Function using ORDER BY clause
  • Group Function using HAVING clause

Exercise 3 - Group Data using GROUP BY Clause

The GROUP BY clause is specified when you want to group selected rows based on the value of one or more expressions for each row and return a single row of summary information for each group.

In this exercise you will learn the following about the group functions:

  • Uses of GROUP BY clause in a SELECT statement.
  • Nesting of Grouped Function within another grouping function.

Exercise 4 - Include or Exclude Grouped Rows

When GROUP BY clause is present in SQL statement, HAVING clause can also be used along with it. The HAVING clause is used to restrict the groups of returned rows to those for which the specified condition is TRUE. If the GROUP BY and HAVING clause both are specified they can appear in either order. If a HAVING clause contains a subquery, the subquery is resolved before evaluating the HAVING clause.

In this exercise you will learn the following about the group functions:

  • Using HAVING clause
  • To restrict the groups of returned rows

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.