Connor Connor - 1 year ago 67
SQL Question

Calculate percentage of values in list that were found?

Say I have a query such as:-

SELECT id FROM Table WHERE id IN (1, 2, 3, 4);

How can I then have SQL return a value which is the percentage of the values in that list that were found in the table, i.e. 0.75 or 75? Any help is appreciated.

vkp vkp
Answer Source

To get the number of id's which exist in the table, use count(distinct id) or count(id) depending on the uniqueness of id in the table. Divide the count by the total number of id's being passed in if you know it beforehand (like 4 in this case).

select 1.0*count(distinct id)/4 as pct_in_table 
from Table 
WHERE id IN (1, 2, 3, 4)