Скачать или смотреть How to Calculate Percentage in SQL with Ease

Discover how to effectively calculate percentages across rows in SQL, utilizing window functions and group aggregates for accurate data analysis.
---
This video is based on the question https://stackoverflow.com/q/76281102/ asked by the user 'Inioluwa Tonade' ( https://stackoverflow.com/u/20004386/ ) and on the answer https://stackoverflow.com/a/76281144/ provided by the user 'lemon' ( https://stackoverflow.com/u/12492890/ ) 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: How do I calculate percentage in SQL?

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.
---
How to Calculate Percentage in SQL with Ease

Calculating percentages in SQL might seem daunting at first, especially when dealing with rows that contain repeated entries. However, with the right understanding and approach, you can calculate the contribution of each item to a total effectively. In this guide, we’ll go through a step-by-step solution to calculating the percentage of different goods_services in relation to their respective total amounts in a database.

Understanding the Problem

Imagine you're working with a table that has the following structure:

Column one: date (stored as varchar, expressed in words).

Column two: amount (int representing monetary values).

Column three: goods_services (various types of services or goods sold).

In this setup, you may see instances where a specific date appears multiple times due to different values for various goods_services. Your task is to find out what percentage each goods_service contributes to the total amount for that date.

For example:

Savings might contribute 10% to the total amount, while Feeding might contribute 20%.

Your goal is to retrieve this percentage through a SQL query.

Solution Approach

To achieve this, follow the outlined steps below, utilizing SQL's powerful window functions and aggregation capabilities.

Step 1: Aggregate Amounts by goods_services

First, you need to calculate the total amount for each type of goods_services. This can be done using the SUM() function in conjunction with the GROUP BY clause.

Step 2: Calculate the Total Amount

Next, use a window function to calculate the overall total amount. The SUM() OVER() syntax will help you find the total regardless of the grouping you're doing in the previous step.

Step 3: Calculate the Percentage

Finally, divide the aggregated amount of each goods_service by the total amount, and multiply by 100 to get the percentage.

Implementing the Solution in SQL

Here's how you can structure your SQL query to put it all together:

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

Explanation of the Query

SELECT Goods_services: This selects the distinct goods_services from your table.

SUM(Amount): This aggregates the amount values for each goods_services.

SUM(SUM(Amount)) OVER(): This computes the total amount across the entire dataset without losing the grouping.

100.0 * [Aggregate Amount] / [Total Amount] AS perc: This calculates what percentage the total amount of each goods_service contributes to the overall amount, providing clear percentage data.

Making Adjustments

Ordering Results: You might want to add ORDER BY perc DESC at the end of your query if you wish to view the results from the highest to lowest percentage contributions.

Filtering Results: If you're interested in specific dates or conditions, don't hesitate to add WHERE clauses as needed.

Conclusion

Calculating percentages in SQL using the aggregation of amounts and leveraging window functions can significantly streamline data analysis tasks. With the right approach, you can extract meaningful insights from your datasets, revealing how each goods_service contributes to total sales.

Feel free to adapt the provided query to fit your specific database needs, and don't hesitate to experiment with different SQL functionalities to achieve your data goals!