sqlite calculating percentage

Question

I would like to select items from a table where their frequency count is less than 5% of the total amount.

For example, if I had a table of names, I would like to select the names that appeared less than 5% of the total.

Answer 1

A simple GROUP BY would suffice I think:

SELECT name, 
       1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) AS percentage
FROM names
GROUP BY name
HAVING 1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) < 0.05 ;

Answer 2

In some cases multiplying by 1.0 may not work casting is more reliable

SELECT name, 
       (cast COUNT(*) as real)/ (SELECT cast (COUNT(*)as real) FROM names) AS percentage
FROM names
GROUP BY name
HAVING (cast COUNT(*) as real)/ (SELECT cast (COUNT(*)as real) FROM names);