- 4 years ago
- Zaid Bin Khalid
- 2035 Views
-
4
In this session, you will learn how to group rows based on column values.
SQL GROUP BY Clause.
The GROUP BY clause is a SQL command that is used to group rows that have the same values.
In other words, we can say that the GROUP BY clause is used in the SELECT statement. Optionally it is used in conjunction with aggregate functions to produce summary reports from the database.
GROUP BY Syntax.
The syntax for a primary GROUP BY query as mentioned below.
SELECT statements... GROUP BY column_name1[,column_name2,...] [HAVING condition];
Grouping Rows.
With the help of SELECT Statement in Group by clause together, you will find out the column values in a group row.
To understand this easily, let’s look at the following table called employees and department tables.
+--------+--------------+------------+---------+
| emp_id | emp_name | hire_date | dept_id |
+--------+--------------+------------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 4 |
| 2 | Tony Montana | 2002-07-15 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 3 |
| 5 | Martin Blank | 2008-06-24 | NULL |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
Proposed Example – Group By.
Now, let’s check out instead of finding just the name of the employees and their departments, you want to find out the total number of employees in every department.
Let suppose in some cases of small tables you can easily apply the LEFT JOIN statement and count the number of employees, but let’s suppose if a table contains thousands of employees, then executing the results would not be so easy
In this type of case, you can use the GROUP BY clause with the SELECT statement, like this.
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name;
Resulting Output.
Now if you execute the above statement, you will get the output something like this.
+-------------------+-----------------+
| dept_name | total_employees |
+-------------------+-----------------+
| Administration | 1 |
| Customer Service | 0 |
| Finance | 1 |
| Human Resources | 1 |
| Sales | 1 |
+-------------------+-----------------+
Summary.
- The GROUP BY Clause is used to group rows with the same values.
- The GROUP BY Clause is used together with the SQL SELECT statement.
- The SELECT statement used in the GROUP BY clause can only be used to contain column names, aggregate functions, constants, and expressions.
- The HAVING clause is used to restrict the results returned by the GROUP BY clause.
Points to Ponder – Note
In SQL GROUP by clause is used with the SELECT statement. In the SQL queries, the GROUP BY clause used after WHERE clause and before the ORDER BY clause.
- 4 years ago
- Zaid Bin Khalid
- 2035 Views
-
4