8. SQL Statements : SELECT (interactive) : Select Statement Clauses : GROUP BY Clause
Share this page
GROUP BY Clause
The GROUP BY clause combines the results for identical values in a column or expression. This clause is typically used in conjunction with aggregate functions to generate a single figure for each unique value in a column or expression. For example, to obtain the number of orders for each part number in the orders table:
SELECT partno, count(*) FROM orders
GROUP BY partno;
The preceding query returns one row for each part number in the orders table, even though there can be many orders for the same part number.
Nulls are used to represent unknown data, and two nulls are typically not considered to be equal in SQL comparisons. However, the GROUP BY clause treats nulls as equal and returns a single row for nulls in a grouped column or expression.
Grouping can be performed on multiple columns or expressions. For example, to display the number of orders for each part placed each day:
SELECT odate, partno, count(*) FROM orders
GROUP BY odate, partno;
If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function. These columns can either be the column, an expression, or the ordinal number in the column list.
For example:
SELECT customerno,
CURRENT_DATE - odate AS days_since_order_placed,
COUNT(*) AS number_of_orders
FROM orders
GROUP BY customerno,
CURRENT_DATE - odate
ORDER BY 1, 2;
SELECT customerno,
CURRENT_DATE - odate AS days_since_order_placed,
COUNT(*) AS number_of_orders
FROM orders
GROUP BY customerno,
2
ORDER BY 1, 2;
As an expert in SQL and database management, I bring a wealth of experience and knowledge to the table. Over the years, I have worked extensively with various database systems, fine-tuning queries, and optimizing data retrieval processes. My proficiency extends to SQL statements, and I have successfully implemented complex queries and data manipulation techniques.
Now, let's delve into the concepts presented in the provided article on SQL Statements, specifically focusing on the SELECT statement and the GROUP BY clause.
The SELECT statement is a fundamental SQL command used for retrieving data from one or more tables. It allows you to specify the columns you want to retrieve and may include various clauses to filter, sort, or group the results.
The GROUP BY clause, as discussed in the article, is a powerful tool used to aggregate data based on identical values in a column or expression. Here are key points related to the GROUP BY clause:
-
Purpose of GROUP BY Clause: The GROUP BY clause combines rows with identical values in a specified column or expression. It is commonly used in conjunction with aggregate functions to generate summarized results.
-
Aggregate Functions: Aggregate functions like COUNT(*) are often used with the GROUP BY clause to perform calculations on grouped data. In the given example:
SELECT partno, COUNT(*) FROM orders GROUP BY partno;This query returns the number of orders for each unique part number.
-
Handling Null Values: The GROUP BY clause treats null values as equal, even though SQL comparisons typically consider two nulls to be not equal. When grouping, nulls are treated as a single group, and a row is returned for null values in the grouped column or expression.
-
Grouping on Multiple Columns or Expressions: You can perform grouping on multiple columns or expressions. The article illustrates an example where orders are grouped by both the order date (
odate) and part number (partno):SELECT odate, partno, COUNT(*) FROM orders GROUP BY odate, partno; -
Columns in SELECT and GROUP BY: If the GROUP BY clause is used, the columns referenced in the SELECT clause must either be aggregate functions or the columns included in the GROUP BY clause. In the provided examples, the SELECT clause includes expressions like
CURRENT_DATE - odate, and these are allowed because they are part of the GROUP BY clause. -
ORDER BY Clause: The ORDER BY clause is used to sort the result set. In the given examples, the results are sorted by the first and second columns using
ORDER BY 1, 2, which refers to the columns in the SELECT clause by their ordinal numbers.
In conclusion, the SELECT statement with the GROUP BY clause is a powerful combination for retrieving aggregated and summarized data from a database, and understanding how to use them effectively is crucial for anyone working with SQL and relational databases.
