When working with databases, you might need to analyze data not only by raw counts but also by their relative proportions. For instance, suppose you're analyzing product sales and want to see how each product contributes to the overall sales.

In this blog post, I'll show you how to augment a MySQL query to calculate relative percentages alongside raw counts.

The Problem

Here’s a simple query that counts sales for each product:

SELECT
  product_name,
  COUNT(*) AS count
FROM
  sales
WHERE
  YEAR(sale_date) = 2024
GROUP BY
  product_name
ORDER BY
  count DESC;

This query gives us the total count of sales for each product, but what if we wanted to see the relative percentage of each product's sales? For example, if one product accounts for 40% of all sales, how do we calculate and display that percentage?

The solution: adding relative percentages

To include relative percentages, we can use window functions. These allow us to calculate a total count for all rows while still displaying individual group counts. Here's the enhanced query:

SELECT
  product_name,
  COUNT(*) AS count,
  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS relative_percentage
FROM
  sales
WHERE
  YEAR(sale_date) = 2024
GROUP BY
  product_name
ORDER BY
  count DESC;

Breaking it down

Let’s dissect the key parts of this query:

1. COUNT(*) AS count
   This computes the raw count for each product_name.

2. SUM(COUNT(*)) OVER ()
   The SUM(COUNT(*)) OVER () computes the total count across all rows, without resetting for each group. This total is used as the denominator to calculate the percentage.

3. COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()
   This formula calculates the relative percentage of each group's count by dividing it by the total count and multiplying by 100. The 100.0 ensures the result is a floating-point number.

4. GROUP BY and ORDER BY
   The query groups the data by product_name and sorts the results in descending order of counts.

Sample Output

If the sales data looks like this:

The updated query will produce:

Why this approach works

Using window functions is both efficient and elegant:

• It avoids calculating totals in a separate query or using subqueries.
• It keeps the logic concise and readable.
• It works seamlessly with grouped data.

Conclusion

Adding relative percentages to your MySQL query results provides valuable context. Whether you're analyzing product sales, user behavior, or any grouped dataset, this technique will enhance your insights.

Try integrating this method into your SQL workflows today and unlock deeper understanding from your data!