Home > Database > Mysql Tutorial > How to Calculate the Percentage of Matching Values in a SQL SELECT Query?

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

Mary-Kate Olsen
Release: 2024-12-27 03:49:09
Original
851 people have browsed it

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;
Copy after login

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;
Copy after login

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
Copy after login

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;
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template