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. 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.

    People are also reading:

    Previous Tutorial Next Tutorial