GROUP BY clause

SQL clause is the specific part of a SQL statement which is used to perform various operations. It can be combined to create more complex queries to retrieve and manipulate data.

GROUP BY clause :

GROUP BY clause is used to group and summarize the data based on column(s) and generally combined with aggregate functions such as SUM, AVG, COUNT, MIN and MAX etcetera to calculate the summaries of the grouped data.

It is often used with other clauses such as 'SELECT', 'FROM', 'HAVING' and 'ORDER BY' etc. to retrieve, specify table, filter, sort and manipulate data.

The syntax of the GROUP BY clause generally looks like this :

SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);
        

• Specify the column(s) name after the 'SELECT' keyword.

• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.

• Specify the table name after the 'FROM' keyword.

• Specify the column name(s) that you want to group and summarize after the 'GROUP BY' keyword.

Here's an example of how you might use the GROUP BY clause :

Let's assume we have a table named "[Employees]".

Case - 1 :

Let's assume you want to determine the number of employees by gender to know how many employees are male or female in the [Employees] table.

Run below SQL statement :

SELECT Gender, COUNT(Gender) AS [Gender_Count]
FROM [Employees]
GROUP BY Gender;
        

In above statement, we used 'COUNT' function that returns the number of rows. Here, we specified the 'Gender' column in COUNT function.

'Gender column' is specified in both of the SELECT and GROUP BY clauses that group and summarize the data by gender and 'COUNT' function returns the count of 'Male' and 'Female' employees separately.

That's it! You have successfully retrieved the number of employees by gender from [Employees] table.

Case - 2 :

Let's assume you want to determine the total salary of employees by gender to know how much male or female employees earns in the [Employees] table.

Run below SQL statement :

SELECT Gender, SUM(Salary) AS [Salary_By_Gender]
FROM [Employees]
GROUP BY Gender;
        

In above statement, we used 'SUM' function that calculates the total sum of numeric values. Here, we specified the 'Salary' column in SUM function.

'Gender column' is specified in both of the SELECT and GROUP BY clauses that group and summarize the data by gender and 'SUM' function calculates the total sum of 'Male' and 'Female' employees salary separately.

That's it! You have successfully calculated the sum of employees salary by gender from [Employees] table.

Case - 3 :

Let's assume you want to determine the highest salary of employees by gender to know what is the highest salary a male or female employee earns in the [Employees] table.

Run below SQL statement :

SELECT Gender, MAX(Salary) AS [Max_Salary_By_Gender]
FROM Employees
GROUP BY Gender;
        

In above statement, we used 'MAX' function that returns the maximum value of a column. Here, we specified the 'Salary' column in MAX function.

'Gender column' is specified in both of the SELECT and GROUP BY clauses that group and summarize the data by gender and 'MAX' function returns the maximum value of 'Male' and 'Female' employees salary separately.

That's it! You have successfully retrieved the maximum value of employees salary by gender from [Employees] table.

Frequently Asked Questions :

GROUP BY clause is used in SQL to group rows that have the same values into summary rows, with an example like "SELECT department, AVG(salary) FROM employees GROUP BY department;"

GROUP BY HAVING clause filters grouped rows based on specified conditions, helpful for filtering aggregated data, such as "SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;"

GROUP BY 1 refers to grouping by the first column specified in the SELECT statement, such as "SELECT department, AVG(salary) FROM employees GROUP BY 1;"

"Clause" is a general term in SQL referring to syntactical elements like SELECT or WHERE, whereas GROUP BY clause specifically organizes data into groups for aggregation.