How Can I Efficiently Calculate Percentages from a COUNT() in a Single SQL SELECT Statement?

Computing Percentages from SUM() in the Same SELECT Statement in SQL

In the context of the table my_obj with integer fields value_a and value_b, it is possible to calculate the percentage of rows where value_a equals value_b. The initial query attempted to achieve this using a stored procedure, but faced an error.

Simplified and More Efficient Solution

A simpler and faster approach is as follows:

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

This query:

• Computes the numerator of the percentage by counting the number of rows where value_a equals value_b using the expression count(value_a = value_b OR NULL). The OR NULL ensures that NULL values are treated as FALSE in the equality comparison.
• Calculates the denominator of the percentage using count(*), which counts all rows, including those with NULL values.
• Divides the numerator by the denominator to obtain the percentage and assigns it to the alias pct.

Handling NULL Values

This solution handles NULL values appropriately, unlike the original query which could lead to incorrect results or division-by-zero errors.

Preserving Decimal Digits

To preserve decimal digits in the result, modify the query as follows:

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

This alteration multiplies the numerator by 100.0 to force numeric precision and then uses round() to specify the number of decimal digits to be preserved.

Conclusion

By employing the simplified query or the modified query with decimal preservation, one can effectively calculate percentages from SUM() within the same SELECT statement, handling NULL values appropriately.

The above is the detailed content of How Can I Efficiently Calculate Percentages from a COUNT() in a Single SQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!