How to Efficiently Calculate Percentages of Matching Rows in PostgreSQL?

Percentage Computation from SUM() Within a PostgreSQL Query

Understanding the Query

The provided query aims to compute the percentage of rows where value_a and value_b in the my_obj table are equal. However, it encounters an error because the nb_ok column, which is referenced in the compute_percent stored procedure, does not exist.

Optimized Solution

To address this issue, consider a more efficient approach:

SELECT property_name,
       (COUNT(value_a = value_b OR NULL) * 100) / COUNT(*) AS pct
FROM my_obj
GROUP BY property_name;

Explanation

This query:

• Counts the number of rows where value_a = value_b using COUNT(...).
• Handles NULL values by using the expression (value_a = value_b OR NULL).
• Divides the number of matching rows by the total count of rows using (COUNT(...) * 100) / COUNT(*).
• Calculates the percentage without the need for an external function.
• Groups the results by property_name to display percentages for each property group.

Including Fractional Digits

To preserve fractional digits in the result, use the following modified query:

SELECT property_name,
       ROUND((COUNT(value_a = value_b OR NULL) * 100.0) / COUNT(*), 2) AS pct
FROM my_obj
GROUP BY property_name;

This version uses ROUND(...) to round the percentage to two decimal places.

The above is the detailed content of How to Efficiently Calculate Percentages of Matching Rows in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!