Calculating New vs Old

ryanschefke

Hello,

How can I construct this query?

I have a bunch of values in a table. I want to output the % of values that only occur once to the % of values that occur more than once. In other words, if I have 1, 2, 2, 2, 3, 4, 4, I would like to output:

New numbers: 50%
Existing numbers: 50%

..new numbers because the 1 and 3 only occurred once and existing numbers of 2 and 4 because they occurred more than once. %s are %s of individual numbers.

What is the query I should use?

Ryan

laserlight

Given a table t with a column c, the best I can come up with at the moment is two possible solutions:

SELECT e.n / a.n FROM
    (SELECT COUNT(e1.c) AS n FROM
        (SELECT c FROM t GROUP BY c HAVING COUNT(c) > 1) e1) e,
    (SELECT COUNT(DISTINCT c) AS n FROM t) a;

and:

SELECT COUNT(DISTINCT e.c) / COUNT(DISTINCT t.c) FROM t,
    (SELECT c FROM t GROUP BY c HAVING COUNT(c) > 1) e;

Both of them compute the fraction of the number of repeated values over the total number of values. Subtract this fraction from 1 and you get the other result that you want. However, what I propose may not necessarily be the best query possible.