GROUP BY in SQL Made Easy: A 101 Tutorial for Beginners

Group by in SQL query language is a clause commonly used to perform an aggregate function like sum, count, max, etc on a group of rows grouped on a certain common column/columns across the table.

For example, the below table which contains the details of the books available in a library:

Group By for a single column:

Consider a use case where in you are the librarian and you want to get the count of all the books under each category of the above shown Books table.

The best approach to this is to group by the column Category and count the Book_ID in each of the category.

💡Syntax of Group By Clause:

SELECT column_1, column_2…..aggregate_function(column_n)

FROM table

WHERE {condition}

GROUP BY column_1, column_2…..

In the above use case of fetching the count of all the books in each category, the following query can be used:

Select category,
count(book_id)

from Books

group by category;

The resulting output from the above query will be:

You can also select multiple columns in the Group By clause but it should always be combined with an aggregate function.

Group By for a multiple columns:

Consider this use case where in you want to get the count of books based on their availability under each category of books.

In such cases you can select on multiple columns and group by multiple columns in order to fetch the count for the multiple group by cases.

Here’s what your query will look like:

Select category,
availability,
count(book_id)

from Books

group by category,availability;

As you can see we have grouped by both the category and availability of the books and here’s what the resulting output will look like:

As you can see in the above output table, we see each category is grouped by availability(Available/Checked Out) and the corresponding counts for each combination is present in the count(book_id) column.

Group by is a very useful and versatile function, so have fun exploring it!