SQL Group By Clause

Group By clause is used with SELECT Statement to get the identical data.
Aggregate function like count(), max(), min(), sum() and avg() are also used Group By clause.

Syntax:-


SELECT column(s)
FROM table_name 
WHERE [condition] 
GROUP By coulumn(s)
ORDER BY column(s)

Example:-Suppose you have an employee table that has 7 records.


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | clerk     | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |
+----+------------+-----------+----------------+-----------+

Now you want to get the number of employees belongs to each country


SELECT count(id), country from employees GROUP BY country

Output:-


+-----------+-----------+
| count(id) | country   |
+-----------+-----------+
|         2 | Australia |
|         3 | India     |
|         2 | USA       |
+-----------+-----------+
3 rows in set (0.00 sec)

SQL Group By Clause – Interview Questions

Q 1: What is GROUP BY used for?

Ans: GROUP BY groups rows that have the same values into summary rows.

Q 2: Which functions are commonly used with GROUP BY?

Ans: Aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Q 3: Can GROUP BY be used without aggregates?

Ans: Yes, but it is mainly useful with aggregate functions.

Q 4: Can GROUP BY use multiple columns?

Ans: Yes, grouping can be done on multiple columns.

Q 5: Is GROUP BY mandatory when using aggregates?

Ans: Yes, when selecting non-aggregated columns.