How to Calculate the Percentage of Matching Values in a SQL SELECT Query?

Calculate Percentages from SUM() in the Same SELECT Query

In the my_obj table, where the integer fields value_a and value_b are present, the goal is to determine the percentage of times value_a equals value_b.

Incorrect Attempt:

select sum(case when o.value_a = o.value_b then 1 else 0 end) as nb_ok,
       sum(case when o.value_a != o.value_b then 1 else 0 end) as nb_not_ok,
       compute_percent(nb_ok,nb_not_ok)
from  my_obj as o
group by o.property_name;

This approach fails because the nb_ok column does not exist within the query.

Optimized Solution:

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

Explanation:

• The operator precedence of = over OR ensures that the comparison is evaluated first.
• count(value_a = value_b OR NULL) ensures that NULL values are ignored.
• The division of (count(value_a = value_b OR NULL) * 100) by count(*) calculates the percentage.
• count(*) represents the total number of rows, including NULL values.

Result:

property_name | pct
--------------+----
 prop_1       | 17
 prop_2       | 43

Alternative for Fractional Digits:

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

This variation introduces a fractional digit to preserve decimal places.

The above is the detailed content of How to Calculate the Percentage of Matching Values in a SQL SELECT Query?. For more information, please follow other related articles on the PHP Chinese website!