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

[F1Fan]

This should get you started. I couldn't figure out the percentage part, but you can do that after you get the results:

 

SELECT COUNT(DISTINCT(field)) AS old, (COUNT(field)-COUNT(DISTINCT(field))) AS new, COUNT(field) AS total FROM table

[Barand]

try

SELECT A.newold, COUNT(*)*100/B.totnums as pcent
FROM
(SELECT val,
   CASE COUNT(*) WHEN 1 THEN 'new' ELSE 'old' END as newold
   FROM mytable
   GROUP BY val) as A
JOIN 
(SELECT COUNT(DISTINCT val) as totnums FROM mytable) as B
GROUP BY A.newold

 

with my data

[pre]

+------+

| val  |

+------+

|    2 |

|    3 |

|    5 |

|    4 |

|    2 |

|    5 |

+------+

 

-->

 

'new', 50.0000

'old',  50.0000