SQL GROUP BY and HAVING Clause with Examples
What exactly is the SQL Group by Clause function?
It is possible to group rows that have the same values using the GROUP BY clause, which is a SQL command. In the SELECT statement, the GROUP BY clause is used to group data. This method can also be used in conjunction with aggregate functions to generate summary reports from the database, if desired.
That is exactly what it does: it summarises information from the database.
The queries that contain the GROUP BY clause are referred to as grouped queries, and they only return a single row for each item that is grouped.
SQL GROUP BY Syntax is a SQL grouping operation.
Following our introduction to the SQL GROUP BY clause, let’s have a look at the syntax for a straightforward group by query.
SELECT statements are used to… GROUP BY column name1[,column name2,…] GROUP BY column name2[,column name3,…] [CONDITION IN EXISTENCE]; HERE
The normal SQL SELECT command query begins with the words “SELECT statements…”.
Grouping is accomplished by the use of the “GROUP BY column name1” clause, which is based on column name1.
A third column name, “[,column name2,…],” is optional and reflects extra column names when grouping is performed on more than one column.
“[HAVING condition]” is an optional clause that is used to restrict the rows that are affected by the GROUP BY clause in the GROUP BY clause. It’s comparable to the WHERE clause in that it specifies a location.
Using a Single Column to Form Groups
SQL Group’s impact on performance has been documented to aid in understanding. Let’s run a basic query that retrieves all of the gender entries from the members table using the clause keyword.
SELECT ‘gender’ FROM the list of members
gender
Female \sFemale \sMale \sFemale \sMale \sMale \sMale \sMale \sMale
Consider the following scenario: we want to obtain the unique values for genders. We can use the following query to accomplish our goal –
GRAPHICALLY ORDERING BY “gender” FROM “members” GROUPED BY “gender”
The following results are obtained by running the aforementioned script in MySQL workbench against the Myflixdb database.
gender \sFemale \sMale
It should be noted that only two results have been returned. This is due to the fact that there are only two gender types: male and female. Using SQL’s GROUP BY clause, all of the “Male” members were grouped together and only a single row was returned for each of them. The “Female” members were subjected to the same treatment.
Using many columns to organise your data
Consider the following scenario: we want to acquire a list of movies sorted by category id and the years in which they were published.
Let’s have a look at the results of this simple query.
SUBSTRING SELECT “category id” AND “year released” FROM “movies”
category id year released
1 2011 \s2 2008 \sNULL 2008 \sNULL
2010 \s8 2007 \s6 2007 \s6 2007 \s8 2005 \sNULL
2012 \s7 1920 \s8 NULL \s8 1920
There are numerous duplicates in the above result.
Let’s do the same query in SQL using the group by function –
SELECT ‘category id’,’year released’ from the SELECT clause. FROM THE GENRE OF’movies’ BY ‘category id’ AND ‘year released’; GROUP BY
When we run the above script in MySQL workbench against the myflixdb database, we get the results presented in the following table.
category id year released
NULL 2008 \sNULL 2010 \sNULL 2012 \s1 2011 \s2 2008 \s6 2007 \s7 1920 \s8 1920 \s8 2005 \s8 2007
As shown in our previous example, the GROUP BY clause operates on both the category id and the year of release to detect unique data.
The row is recognised as unique if both the category id and the year of release are the same, else the row is treated as unique.
A duplicate row is regarded to exist if the category id and the year published are the same for more than one row. In this case, just one row is displayed.
Functions for grouping and aggregation
Consider the following scenario: we want to know the total number of males and females in our database. In order to accomplish this, we can use the script provided in the next section.
SUBSTRUCT COUNT(‘membership number’) FROM SELECT ‘gender’ FROM’members’ GROUP BY ‘gender’;
The following results are obtained by running the aforementioned script in MySQL workbench against the myflixdb database.
gender COUNT(‘membership number’)
Female 3 \sMale 5
Gender values are grouped together in the results displayed below, and the number of grouped rows is counted using the COUNT aggregate function to determine the total number of grouped rows.
The HAVING clause is used to restrict the results of a query.
It’s not always the case that we’ll want to execute groups on all of the information in a given table. Occasionally, we will wish to limit the results of our search to to those that meet a specific set of criteria. In such instances, the HAVING clause might be used to our advantage.
Consider the following scenario: we want to know all of the release years for movie category id 8. In order to achieve our goals, we would use the script provided below.
SELECT * FROM’movies’ GROUP BY ‘category id’,’year released’ FROM’movies’ GROUP BY ‘category id’,’year released’ When we run the above script in MySQL workbench against the Myflixdb, we get the following results, which are given below. HAVING ‘category id’ = 8; Executing the above script in MySQL workbench against the Myflixdb gives us the following results, which are shown below.
Honey mooners is a movie with the following identifiers: movie id title director year released category id 9
John Schultz 2005, August 5,
Daddy’s Little Girls NULL 2007 8 Daddy’s Little Girls
Please keep in mind that our GROUP BY clause has only applied to movies with category id 8.
Summary
The GROUP BY clause is a grouping of clauses. The SQL language is used to group rows that have the same values.
The GROUP BY Clause is used in conjunction with the SELECT statement in the SQL language.
The SELECT statement used in the GROUP BY clause can only contain column names, aggregate functions, constants, and expressions, and cannot contain any other types of data.
Using the SQL Having Clause, you can limit the number of results produced by the GROUP BY clause.
Using the GROUP BY Clause in MySQL, you can gather information from numerous records and sort the resultant record set by one or more columns.