Group By in SQL With Example [ COUNT, AVG, MAX, MIN ,SUM ] Video

Group By in SQL With Example [ COUNT, AVG, MAX, MIN ,SUM ] Video

Learning Group By in SQL is a little bit tricky for a newbie programmer. In today’s lesson, I will give in-depth discussion about Group By in SQL with Example using Aggregate functions.

Few Questions and Answers in learning SQL Group By Functions

What is Group By in SQL?

Group functions in sql are mathematical functions to operate on sets of rows to give one result per set. ex: “Find the number of employee in each Department”.

What Group By Does in SQL?

It arrange identical data into groups with the help of some aggregate functions.

What is Aggregate Functions in SQL?

is a function where the values of multiple rows are grouped together to form a single summary value.

Where Group By Having in SQL?

The HAVING clause is used to specify which groups are to be displayed, and thus, you further restrict the groups on the basis of aggregate information.

Lesson Objectives

Here’s the main objectives after completing this lesson, you will be able to do the following:

  • Identify the available Group Functions
  • Describe the use of group functions
  • Aggregate data using GROUP BY clause
  • Include or Exclude grouped rows by using the HAVING clause

Types of group functions

The GROUP BY statement is often used with aggregate functions (AVG, COUNT, MAX, MIN, and SUM) to group the result-set by one or more columns.

Group by in SQL Aggregate Function

  • COUNT

    Calculate the number of rows in a set

  • AVG

    Calculates the average of the specified columns in a set of rows.

  • MAX

    Returns the largest value of the selected column.

  • MIN

    Returns the smallest value of the selected column.

  • SUM

    Returns the total sum of a numeric column.

Group Functions Syntax

SELECT (column,...), group_function(column)
FROM (table)
WHERE (condition)
[GROUP BY (column)]
[ORDER BY (column)]

Sample table used in this lesson

EMP_ID EMPNAME CONTACT SALARY DEPT_NAME
1 Kobe Bryant 7895412 50000.00 SHIPPING
2 Jordan Michael 7895412 70000.00 IT
3 James BOnd 1232456 45000.00 SALES
4 Anne Curtis 7878898 35000.00 SHIPPING
5 John Wick 12334557 65000.00 EXECUTIVE
6 Hatch Glets 66548782 57000.00 IT
7 Xyre Anderson 78778923 48000.00 SALES
TABLE NAME: tblsampleemployee

Watch the Video demonstration here:

https://youtu.be/BCCIBUgNDzs

Count with group by statement

The following SQL statement finds the number of Employee by Department:

SQL Group by Count Statement

SELECT DEPT_NAME, COUNT(EMP_ID) AS "No of Employee" FROM tblsampleemployee GROUP BY DEPT_NAME

Results of Count with group by Statement

DEPT_NAME No of Employee
EXECUTIVE 1
IT 2
SALES 2
SHIPPING 2

SQL Group by using AVG

The following SQL statement finds the average SALARY of all Employee:

SQL statement using AVG

SELECT AVG(SALARY) AS "Average Salary" FROM tblsampleemployee

RESULT

Average Salary
52857.142857

AVG with Group By in SQL Example

The following SQL statement finds the average SALARY of all Employee by Department:

Another SQL statement using AVG

SELECT DEPT_NAME, AVG(SALARY) AS "Average Salary" 
FROM tblsampleemployee GROUP BY DEPT_NAME

SQL group by Example Results

DEPT_NAME Average Salary
EXECUTIVE 65000.000000
IT 63500.000000
SALES 46500.000000
SHIPPING 42500.000000

Query using MAX AND MIN With group by statement

The following SQL statement finds the Maximum and Minimum Salary by Department:

Group by in SQL Statement

SELECT DEPT_NAME, MAX(SALARY) AS "Maximum Salary", MIN(SALARY) AS "Minimum Salary" FROM tblsampleemployee GROUP BY DEPT_NAME

Results of Using MAX and MIN

DEPT_NAME Maximum Salary Minimum Salary
EXECUTIVE 65000.00 65000.00
IT 70000.00 57000.00
SALES 48000.00 45000.00
SHIPPING 50000.00 35000.00

Query using SUM With Group by statement

The following SQL statement finds the Payroll by Department:

Group by statement using SUM

SELECT 
DEPT_NAME, sum(SALARY) AS "PAYROLL" 
FROM tblsampleemployee 
GROUP BY DEPT_NAME 
ORDER BY DEPT_NAME

Results

DEPT_NAME PAYROLL
EXECUTIVE 65000.00
IT 127000.00
SALES 93000.00
SHIPPING 85000.00

Excluding group results: The having clause

Use the HAVING clause to restrict groups:

  1. Rows are grouped.
  2. The group function is applied.
  3. Groups matching the Having clause are displayed.

Syntax of Group by HAVING in SQL

SELECT (column,...), group_function(column)
FROM (table)
WHERE (condition)
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY (column)]

Example: The following SQL statement finds the Total Payroll exceeding 80,000.00 by Department.

SQL Statement Using Having Clause

SELECT
DEPT_NAME, sum(SALARY) AS "PAYROLL"
FROM tblsampleemployee
GROUP BY DEPT_NAME
HAVING SUM(SALARY) > 80000
ORDER BY DEPT_NAME

Results of using Having Clause

DEPT_NAME PAYROLL
IT 127000.00
SALES 93000.00
SHIPPING 85000.00

Based on the example of using HAVING Clause, it displays the DEPT_NAME and PAYROLL that exceeds 80000. You will also notice that the EXECUTIVE department has been excluded and the list is sorted by Department Name.

NOTE: You can use the HAVING clause to specify which groups are to be displayed, and thus, you further restrict the groups on the basis of aggregate information.

Summary

In this lesson, you should have learned how to:

  • Use the functions AVG, COUNT, MAX, MIN, SUM
  • Write queries that use the Group By clause
  • Write queries that use the Having Clause

Related Article

Inquiries

I hope that you have learned something new today. If you have any questions or suggestions about the Group by in SQL with examples, please feel free to leave a comment below.

1 thought on “Group By in SQL With Example [ COUNT, AVG, MAX, MIN ,SUM ] Video”

Leave a Comment