How Can I Efficiently Calculate Percentages Using SUM() in a Single PostgreSQL Query?

Calculating Percentages from SUM() in a Single SQL Query

In PostgreSQL, you may encounter an error when attempting to compute percentages using SUM() within the same SQL query. The problem arises when you try to access a column that doesn't yet exist, such as nb_ok in your example.

Optimized Solution

A more efficient and accurate approach to calculate percentages is to use the following query:

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

This query takes the following steps:

1. Counts the number of rows where value_a equals value_b, regardless of NULL values, using count(value_a = value_b OR NULL).
2. Divides the result by the total number of rows in the table using count(*).
3. Multiplies the result by 100 to express the ratio as a percentage.

Handling NULL Values

The use of OR NULL in the count() expression ensures that NULL values are not counted. This is important if your table contains NULL values, as it prevents incorrect results or division-by-zero errors.

Forcing Fractional Digits

To preserve fractional digits in the result, you can use 100.0 instead of 100, resulting in a numeric calculation that avoids truncation:

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

Avoiding Case-Insensitive Identifiers

When using PostgreSQL, it's recommended to avoid unquoted mixed-case identifiers, such as valueA. Instead, use lowercase identifiers to avoid potential errors and confusion.

The above is the detailed content of How Can I Efficiently Calculate Percentages Using SUM() in a Single PostgreSQL Query?. For more information, please follow other related articles on the PHP Chinese website!