SQL: Calculating Percentages with Code Examples

SQL, or Structured Query Language, is a powerful tool for managing and analyzing data stored in relational databases. One common task in data analysis is calculating percentages, which can provide valuable insights into the distribution of data. In this article, we’ll explore how to calculate percentages using SQL with practical code examples.

Understanding Percentages

Before we dive into SQL code examples, let’s briefly understand the concept of percentages. A percentage is a ratio expressed as a fraction of 100. It’s often used to represent proportions, comparisons, and distributions in data.

Calculating Percentages in SQL

Let’s calculate the percentage contribution of each store to total sales.

SELECT stor_id, SUM(qty) AS total_qty, (SUM(qty) / (SELECT SUM(qty) FROM sales) * 100) AS percentage
FROM sales
GROUP BY stor_id
ORDER BY SUM(qty) DESC;

Let’s calculate the percentage contribution of female and male actors.

SELECT gender, COUNT(gender), COUNT(gender) / (SELECT COUNT(gender) FROM actors) * 100 AS percentage
FROM actors
GROUP BY gender;

Let’s assume we have a database table called sales that contains information about products and their sales quantities. We want to calculate the percentage of each product's sales contribution to the total sales.

SELECT product_name,
       sales_quantity,
       (sales_quantity / SUM(sales_quantity) OVER ()) * 100 AS sales_percentage
FROM sales;

In this example, the SUM(sales_quantity) OVER () window function calculates the total sales quantity across all products. The sales_percentage column is computed by dividing each product's sales_quantity by the total sales quantity and then multiplying by 100 to get the percentage.

Grouped Percentages

Calculating percentages within groups is also common in data analysis. Let’s say we want to find the percentage of sales for each product category.

SELECT category,
       SUM(sales_quantity) AS total_sales,
       (SUM(sales_quantity) / SUM(SUM(sales_quantity)) OVER ()) * 100 AS category_percentage
FROM sales
GROUP BY category;

In this query, the SUM(SUM(sales_quantity)) OVER () window function calculates the total sales quantity across all categories. The category_percentage column is computed by dividing the sum of each category's sales quantity by the total sales quantity and then multiplying by 100.

Filtering with Percentages

You can also filter results based on calculated percentages. For instance, let’s find products that contribute more than 10% of total sales.

SELECT product_name,
       sales_quantity,
       (sales_quantity / SUM(sales_quantity) OVER ()) * 100 AS sales_percentage
FROM sales
WHERE (sales_quantity / SUM(sales_quantity) OVER ()) * 100 > 10;

This query calculates the sales percentage for each product and then filters the results using a WHERE clause to show products with a sales percentage greater than 10%.

Conclusion

Calculating percentages in SQL is a valuable skill for data analysts and database professionals. It allows you to gain insights into data distributions and make informed decisions based on proportions. Whether you’re analyzing sales data, survey responses, or any other dataset, SQL provides the tools to perform these calculations efficiently.

SQL Fundamentals

Thank you for your time and interest! 🚀 You can find even more content at SQL Fundamentals 💫