GROUP BY in SQL is also used with the aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result by one or more columns. Its main work is to summarize the data from the database.
The Group By statement allows you to arrange the rows of a query in the groups. The groups are determined by the table columns specified in a GROUP BY clause.
SQL Group By
SQL GROUP BY clause is used to arrange the same data into groups with the help of some functions. For example, the GROUP BY clause groups rows with the same values into summary rows, like “find the number of customers in each city.”
To build some charts, use the GROUP BY clause in SQL, which will be helpful to summarize the data and then draw the chart based on the fetched data.
The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.
- GROUP BY statement is used with a SELECT statement.
- The GROUP BY statement is placed after the WHERE clause in the query.
- The GROUP BY statement is placed before the ORDER BY clause if used in the query.
SELECT select_columns FROM table GROUP BY column_name1, column_name2 ,...;
In the above query, the GROUP BY statement produced the group for each combination of the values in the columns listed in a GROUP BY clause.
Now, let’s go to the example.
First, we need to create two tables. If you do not know how to create the SQL table, check out the SQL Create Table tutorial. Now, create the first table.
We have our products dummy data. See the below data.
We have the products table with ProductID, ProductName, ProductPrice, and created_at columns.
Consider the following GROUP BY example.
SELECT ProductName, YEAR (created_at) OrderYear FROM Products
See the output.
We have selected the ProductName and just Year from the date.
We can add further constraints like Order By. See the following query.
SELECT ProductID, ProductName, YEAR (created_at) OrderYear FROM Products ORDER BY ProductID DESC
See the following example.
In this example, we retrieved the ProductID, ProductName, and the ordered year of the Products. As you can see clearly from the outcome, the Products with the ProductID Order by descending.
Now, see the following query.
SELECT ProductID, YEAR (created_at) OrderYear, ProductPrice FROM Products GROUP BY ProductPrice ORDER BY ProductPrice DESC
See the below output.
In the above query, we have selected three columns: Group By ProductPrice.
We have used the GROUP By and ORDER BY clauses.
Now, let’s modify the database. First, we will add some more data.
See the new database.
For example, let’s use the Count aggregate function to count the number of elements.
See the below example.
SELECT COUNT(ProductID) as TotalID, ProductName, Year(created_at) FROM Products GROUP BY ProductName ORDER BY created_at
In the above query, we have selected three columns.
We are selecting the no. of ids with the same number of Products.
So, we have used the GROUP BY clause with the ProductName column name.
Thus, the count function will count the rows with the same products, give the total, and print in the output.
See the below output.
The above output shows that the FRONTROW SNEAKER product has appeared twice, and LV INITIALS looks only once.
The GROUP BY clause is also used with aggregate functions for generating summary reports.
The GROUP BY clause arranges rows into groups, and the aggregate function returns each group’s summary (count, min, max, average, sum, etc.).
If you want to refer to any other column or expression not listed in the GROUP BY statement, you must use that column as an input of the aggregate function.
Otherwise, you will get the error because there is no guarantee that the column or expression will return a single value per group.
Using GROUP BY clause with the MIN and MAX functions example
See the following query.
SELECT ProductID, COUNT(ProductID) as Total, ProductName, MIN(ProductPrice) as min_price FROM Products GROUP BY ProductName ORDER BY ProductID
In the above query, we are selecting four columns. We select a group by ProductName and count the total products with their minimum price. See the below output.