GROUP BY query in sql
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];
- "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` ;
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.
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` ;
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.
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.
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.
|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.
- 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.