Скачать или смотреть Understanding How to Calculate the Average Number of Borrowed Books by Occupation in MySQL

Discover how to effectively calculate the `average borrowed books` per occupation from your database tables using SQL statements in this step-by-step guide.
---
This video is based on the question https://stackoverflow.com/q/62661997/ asked by the user 'Scott Daily' ( https://stackoverflow.com/u/9333790/ ) and on the answer https://stackoverflow.com/a/62662037/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Calculating an average with the result of a group by statement

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction: The Challenge of Calculating Averages in SQL

In the world of databases, one common challenge when working with SQL is calculating averages based on grouped data. For instance, if you want to find out how many books are borrowed on average by individuals within different occupations, the task might seem daunting, especially when dealing with two related tables: one for borrowers and another for clients.

In this guide, we'll walk through how to overcome this challenge using MySQL queries. We'll break it down and guide you step-by-step to successfully retrieve the average number of borrowed books by occupation.

The Database Structure

Before diving into the solution, it’s essential to understand the structure of the database. In our case, we are working with two tables:

Borrower Table:

Fields: BorrowID, ClientID, BookID

This table holds information about who borrowed which book.

Client Table:

Fields: ClientID, Occupation

This table includes information about clients and their respective occupations.

The key connection between these two tables is the ClientID, which serves as a foreign key in the borrower table.

The Initial Queries

To start calculating the average borrowed books per occupation, you first need to gather some initial data. The first query you might run is to count the number of clients in each occupation.

[[See Video to Reveal this Text or Code Snippet]]

Once you have the number of clients for each occupation, the next step is to count the number of books borrowed by clients in each occupation.

[[See Video to Reveal this Text or Code Snippet]]

The Problem: Dividing Counts

While the initial queries successfully provide the count of clients and borrowed books per occupation, you face a challenge: dividing books_per_occupation by occupation_count to get the average. To do this effectively, we will make use of a single SQL query that combines the two requirements without using a sub-query.

The Solution: The Final Query

The key to solving this problem lies in leveraging the COUNT(DISTINCT) function. This function allows you to obtain a correct count of unique clients for the division, resulting in an accurate average of books borrowed per client within each occupation.

Here’s how you can write your final query:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Final Query:

SELECT c.Occupation: We're selecting the Occupation from the client table.

count(b.ClientId): This counts every book that has been borrowed associated with that occupation.

count(distinct c.clientId): This counts unique clients (the basis for our average).

/ (division): The number of borrowed books is divided by the number of unique clients to get the average books borrowed per client.

LEFT JOIN: Ensures that all clients are included even if they haven't borrowed any books.

GROUP BY c.Occupation: This groups the results by each occupation to perform the calculations accordingly.

Conclusion

Calculating the average number of borrowed books by occupation from two related tables might seem complicated at first, but with the right approach in SQL, it can be straightforward. By combining queries effectively and utilizing COUNT(DISTINCT), you can derive meaningful insights from your data.

Now, you can easily see how many books, on average, each profession borrows, providing valuable data for your library or information system.

By following the steps we've outlined, you can confidently tackle similar SQL challenges in your database management tasks. Happy querying!