SQL Group By

By | September 8, 2020
SQL Group By

If we want to fetch or display the table rows by a specific group or by similar data values then we use the SQL GROUP BY command.

For instance, if there is a database table which holds the top-ranked students from the various branch and we want to display all students details according to their branch then we have to use the GROUP BY command to group student details according to their Branch.

Vamware

We often use the GROUP BY statement with the aggregate function such as COUNT, MAX, MIN, SUM, AVG, etc.

GROUP BY syntax

To use the GROUP BY statement, follow this syntax:

SELECT column_name_a
FROM table_name
WHERE [condition]
GROUP BY [column_name_b]
ORDER BY column_name_c;

Here it is completely optional to use the ORDER BY and WHERE  clauses with the GROUP BY Clause. It completely depends on the query whether there is a need to use these other two clauses.

Example

For examples consider this table of students:

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

Query: Display the number of top-ranked students from each trade:

SELECT trade, COUNT(trade) as Number_of_Students
FROM students
GROUP BY trade;

Output

+----------+--------------------+
| trade    | Number_of_Students |
+----------+--------------------+
| Science  |                  2 |
| Humanity |                  3 |
| Commerce |                  1 |
+----------+--------------------+

here using the GROUP BY command we Group the Trade into 3 Distinct categories(Science, Humanity and Commerce)and then from each category, it using the count() aggregate function we count the number of students having the similar trade.

Query: Display the names, marks, trade of individual top student from each branch :

SELECT name, MAX(marks) as marks, trade
FROM students
GROUP BY trade;

Output

+--------+-------+----------+
| name   | marks | trade    |
+--------+-------+----------+
| Luffy  |   970 | Science  |
| Naruto |   960 | Humanity |
| Zoro   |   940 | Commerce |
+--------+-------+----------+

Summary

  • The GROUP BY statement is used to group or categories similar values from the table.
  • It often used with Aggregate functions such as AVG, SUM, MAX, MIN, etc

Leave a Reply

Your email address will not be published. Required fields are marked *