Basic about GROUP BY Clause – using and example

This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.

Description

The SQL GROUP BY clause can be used in a SELECT statement to gather statistics throughout more than one records and crew the effects through one or more columns.

Syntax

The syntax for the GROUP BY clause in SQL is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, … expression_n Expressions that are no longer encapsulated within an aggregate function and must be covered in the GROUP BY Clause at the give up of the SQL statement. aggregate_function This is an combination characteristic such as the SUM, COUNT, MIN, MAX, or AVG functions. aggregate_expression This is the column or expression that the aggregate_function will be used on. tables The tables that you want to retrieve archives from. There have to be at least one desk listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the archives to be selected. ORDER BY expression Optional. The expression used to sort the records in the result set. If extra than one expression is provided, the values have to be comma separated. ASC Optional. ASC varieties the end result set in ascending order by expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC varieties the result set in descending order by using expression.

DDL/DML for Examples

If you desire to comply with alongside with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example – Using GROUP BY with the SUM Function

Let’s look at how to use the GROUP BY clause with the SUM feature in SQL.

In this example, we have a desk called employees with the following data:

employee_number last_name first_name earnings dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT dept_id, SUM(salary) AS total_salaries
FROM employees
GROUP BY dept_id;

There will be 2 archives selected. These are the results that you ought to see:

dept_id total_salaries

500 119500

501 113000

In this example, we have used the SUM function to add up all of the salaries for each dept_id and we’ve got aliased the consequences of the SUM characteristic as total_salaries. Because the dept_id is now not encapsulated in the SUM function, it have to be listed in the GROUP BY clause.

Example – Using GROUP BY with the COUNT function

Let’s look at how to use the GROUP BY clause with the COUNT characteristic in SQL.

In this example, we have a desk referred to as products with the following data:

product_id product_name category_id 1 Pear 50 2 Banana 50 3 Orange 50 4 Apple 50 5 Bread 75 6 Sliced Ham 25 7 Kleenex NULL

Enter the following SQL statement:

Try It

SELECT category_id, COUNT(*) AS total_products
FROM products
WHERE category_id IS NOT NULL
GROUP BY category_id
ORDER BY category_id;

There will be 3 information selected. These are the effects that you see:

category_id total_products

25 1

50 4

75 1

In this example, we’ve got used the COUNT function to calculate the variety of merchandise for each category_id and we’ve aliased the consequences of the COUNT feature as total_products. We’ve excluded any category_id values that are NULL by means of filtering them out in the WHERE clause. Because the category_id is now not encapsulated in the COUNT function, it have to be listed in the GROUP BY clause.

Example – Using GROUP BY with the MIN function

Let’s next seem at how to use the GROUP BY clause with the MIN characteristic in SQL.

In this example, we will use the personnel desk again that is populated the following data:

employee_number last_name first_name income dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT dept_id, MIN(salary) AS lowest_salary
FROM employees
GROUP BY dept_id;

There will be 2 records selected. These are the results that you ought to see:

dept_id lowest_salary

500 57500

501 42000

In this example, we’ve got used the MIN function to return the lowest profits for each dept_id and we have aliased the consequences of the MIN function as lowest_salary. Because the dept_id is now not encapsulated in the MIN function, it must be listed in the GROUP BY clause.

Example – Using GROUP BY with the MAX function

Finally, let’s seem to be at how to use the GROUP BY clause with the MAX function.

Let’s use the personnel desk again, however this time discover the easiest earnings for every dept_id:

employee_number last_name first_name profits dept_id 1001 Smith John 62000 500 1002 Anderson Jane 57500 500 1003 Everest Brad 71000 501 1004 Horvath Jack 42000 501

Enter the following SQL statement:

Try It

SELECT dept_id, MAX(salary) AS highest_salary
FROM employees
GROUP BY dept_id;

There will be 2 information selected. These are the effects that you have to see:

dept_id highest_salary

500 62000

501 71000

In this example, we’ve used the MAX function to return the best earnings for every dept_id and we have aliased the results of the MAX feature as highest_salary. The dept_id column have to be listed in the GROUP BY clause due to the fact it is no longer encapsulated in the MAX function.