Group By in MySQL Queries

In the previous books table, we have added one more attribute category to differentiate between Programming Languages and Theory Papers. The details of the books table are shown below:

The values of the category attribute are Language and Theory. You may have many more categories according to the requirements.

Here, we want to find the number of books available in the library in each category. Based on our previous knowledge, it's difficult to find.

In this context, the solution is to count the books after grouping them according to the categories. Thus group by will be useful to do so.

Let us check the information available in the books table.

To count the number of books in each category, we can execute the following query:

Similarly, if you want to count the number of books according to the publishers, you may try the following SQL query.

You can also use order by along with group by as shown below.

Group By with Having

If you want to modify the above-mentioned query to fetch only those groups where the count is greater than 1, you can't use the where clause. In this case, you have to use having with the group by. The following example shows the result:

Happy Exploring!