GROUP BY query in sql

Feb 4, 2022 - 18:54

0 36

What is item grouping?

The GROUP BY clause is an SQL command that is used to group rows with the same values .

The GROUP BY clause is used in the SELECT statement. It is optionally used in conjunction with aggregate functions to generate summary reports from a database.

Here's what it does by summarizing the data from the database.

Queries that contain a GROUP BY clause are called grouped queries and return only one row for each grouped item.

GROUP BY Syntax

Now that we know what the GROUP By clause is, let's take a look at the syntax for a basic on-demand group.

SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];

HERE

"SELECT statements..." is a standard SQL SELECT command query.
" GROUP BY column_name1 " is a clause that performs a grouping based on column_name1.
"[, column_name2, ...]" is optional; represents other column names when grouping on multiple columns.
"[HAVING condition]" is optional; it is used to limit the rows affected by the GROUP BY clause. This is similar to the WHERE clause.

Grouping using a single column

To understand the impact of the Group By clause, let's run a simple query that returns all gender records from the members table.

SELECT `gender` FROM `members` ;

gender
Female
Female
Male
Female
Male
Male
Male
Male
Male

Suppose we want to get unique values for genders. We can use the following query −

SELECT `gender` FROM `members` GROUP BY `gender`;

Running the above script in MySQL Workbench against Myflixdb gives us the following results.

gender
Female
Male

Notice only two results were returned. This is because we only have two types of gender: male and female. The GROUP BY clause grouped all the "male" members together and only returned one row for it. The same was true for "women".

Grouping using multiple columns

Let's say we want to get a list of movies by category_id and the corresponding years they were released.

Let's look at the output of this simple query

SELECT `category_id`,`year_released` FROM `movies` ;

category_id	year_released
one	2011
2	2008
NULL	2008
NULL	2010
eight	2007
6	2007
6	2007
eight	2005
NULL	2012
7	1920
eight	NULL
eight	1920

The above result has many duplicates.

Let's execute the same query using group by −

SELECT `category_id`,`year_released` FROM `movies` GROUP BY `category_id`,`year_released`;

Running the above script in MySQL Workbench against myflixdb gives us the following results shown below.

category_id	year_released
NULL	2008
NULL	2010
NULL	2012
one	2011
2	2008
6	2007
7	1920
eight	1920
eight	2005
eight	2007

The GROUP BY clause acts on both the category id and the release year to identify the unique rows in our example above.

If the category ID matches, but the released year is different, then the row is considered unique. If the category id and issued year match for more than one row, then it is considered a duplicate and only one row. shown.

Grouping and aggregating functions

Let's say we want the total number of men and women in our database. We can use the following script below to do this.

SELECT `gender`,COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Running the above script in MySQL Workbench against myflixdb gives us the following results.

gender	COUNT('membership_number')
Female	3
Male	5

The results shown below are grouped by each unique gender value published, and the number of grouped rows is counted using the COUNT aggregate function.

Limiting Query Results with the HAVING Clause

We don't always want to group by all the data in a given table. There will be times when we want to limit our results to certain predefined criteria. In such cases, we can use the HAVING clause

Suppose we want to know all the release years for a movie category id 8. We will use the following script to achieve our results.

SELECT * FROM `movies` GROUP BY `category_id`,`year_released` HAVING `category_id` = 8;

Running the above script in MySQL Workbench against Myflixdb gives us the following results shown below.

movie_id	title	director	year_released	category_id
9	honey mooners	John Schultz	2005	eight
5	Daddy's Little Girls	NULL	2007	eight

Note that our GROUP BY clause only affected movies with a category ID of 8.

Summary

The GROUP BY clause is used to group rows with the same value.
The GROUP BY clause is used in conjunction with the SQL SELECT statement.
The SELECT statement used in the GROUP BY clause can only be used with column names, aggregate functions, constants, and expressions.
The HAVING clause is used to limit the results returned by the GROUP BY clause.