Group by Clause in SQL

Overview

The Group BY clause is utilized in a SELECT statement to group rows into a set of summary rows by values of one or more columns. It is regularly utilized in conjunction with aggregate functions such as Count, MAX, MIN, Sum, AVG, etc. The Group BY clause takes after the WHERE clause in a SELECT statement and goes before the Order BY clause.

The SQL GROUP BY

An online retail store has recently started selling products online, and they want to analyze customer orders to understand its customer base better. The store owner has turned to SQL to help them do this. They want to group customer orders by product category to understand better which products their customers buy. To do this, they use the GROUP BY clause to group customer orders by product category. This allows them to quickly identify the most popular product categories and decide how to serve their customers better. Unfortunately, they weren't aware of how to progress. Let's help them.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

The SELECT clause helps specify the columns selected from the table. The WHERE clause helps to restrict the result set by including a condition. The GROUP BY clause is utilized to group the selected rows into groups with the same values for the specified column(s). The ORDER BY clause helps to sort the result set based on the specified column(s).

SQL GROUP BY Example

Table Name: customers

SELECT COUNT(customer_id), country
FROM customers
GROUP BY country;

This SQL statement would return the number of customers in each country. The result would be two columns - one containing the count of customers, and the second containing the country

GROUP BY With JOIN Example

Employees Table

Salaries Table

SELECT employees.last_name, SUM(salaries.salary)
FROM employees
INNER JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY employees.last_name;

This query uses a GROUP BY clause with a JOIN to select the last name of every employee and the total salary of all salaries associated with that employee. It does this by joining the employees and salaries tables on the emp_no column and grouping the result by last_name. The SUM function is then used to calculate the total salary for each employee.

Conclusion

The online retail store utilized SQL and the Group BY clause to analyze client orders and gain a better understanding. By gathering client orders by item category, they could rapidly recognize the most prevalent item categories and choose how to serve their clients better. This permitted the store to better get its client base and make educated choices on how to better serve them.

Key takeaways

1. GroupBy clause is used to aggregate data by grouping records with common values.
2. GroupBy clause should be used after the WHERE clause and before the ORDER BY clause.
3. The GroupBy clause is often used in conjunction with aggregate functions like SUM, AVG, MAX, MIN, etc.
4. The GROUP BY clause can group data by one or more columns.
5. When using the GROUP BY clause, all non-aggregated columns must be included in the GROUP BY clause.
6. The HAVING clause can be used to filter the groups that the GROUP returns BY clause.

Quiz

1. What is the purpose of the GROUP BY clause in a SQL query?  
   1. To group results by a specified field 
   2. To sort results in ascending or descending order 
   3. To limit the number of results returned 
   4. To combine multiple queries into one

Answer: a. To group results by a specified field

2. What type of data can be used in a GROUP BY clause? 
   1. Text 
   2. Numbers 
   3. Dates 
   4. All of the above

Answer: d. All of the above

3. How is the HAVING clause different from the WHERE clause? 
   1. The HAVING clause can be used with aggregate functions  
   2. The WHERE clause can be used with aggregate functions  
   3. The HAVING clause can be used to limit the number of results returned
   4. The WHERE clause can be used to limit the number of results returned

Answer: a. The HAVING clause can be used with aggregate functions

4. What is the purpose of the GROUP BY clause? 
   1. To combine multiple queries into one 
   2. To group results by a specified field 
   3. To sort results in ascending or descending order 
   4. To limit the number of results returned

Answer: b. To group results by a specified field