How to Calculate the Percentage of Matching Values Between Two Columns in a Single SQL SELECT Statement?

Computing Percentages from SUM() Within a Single SELECT Statement in SQL

In the context of the 'my_obj' table, which contains two integer fields ('value_a' and 'value_b'), you seek to determine the percentage of instances where 'value_a' equals 'value_b'.

Simple and Efficient Method

For optimal performance and simplicity, consider the following revised query:

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

This approach leverages the ability of 'count()' to disregard 'NULL' values, ensuring that both columns are accounted for. By multiplying the count of matching values by 100 and dividing by the total count, the percentage is calculated.

Including Fractional Digits

If fractional digits are desired in the result, use '100.0' instead of '100' in the calculation and consider using 'round()':

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

This revised version ensures the calculation remains numeric, preserving fractional values and allowing for greater precision in the reported percentage.

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