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
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”.
It arrange identical data into groups with the help of some aggregate functions.
is a function where the values of multiple rows are grouped together to form a single summary value.
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.
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
Calculate the number of rows in a set
Calculates the average of the specified columns in a set of rows.
Returns the largest value of the selected column.
Returns the smallest value of the selected column.
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
Watch the Video demonstration here:
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|
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
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
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|
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
Excluding group results: The having clause
Use the HAVING clause to restrict groups:
- Rows are grouped.
- The group function is applied.
- 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
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.
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
- Best JOINS In SQL With Examples 2020 | Video Tutorial
- Writing Basic SQL Select Statements With Actual Demonstration
- SQL SELECT Statement WHERE | Restricting and Sorting of Data
- Logical Operators In SQL [ AND, OR, NOT ]
- Best MySQL Tutorial For Beginners in 7 Days
- DBMS Mini Projects Topics for Students 2021
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.